Too few parameters. Expected 1. Error

[Dee]

Running an AfterUpdate event procedure, I get the following error:

"Too few parameters. Expected 1."

My code is as follows:
Private Sub DealerID_AfterU pdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("Q rCheckDlrAssign ed")
Set rs = qd.OpenRecordse t()

If Not rs.EOF Then
Me!Terassigned = rs!Dlr_assigned
End If

On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

End Sub
The query returns only one field from one record of a table with 150
records. The field is a yes/no check field.

The query is a select query called "QrCheckDlrAssi gned".
The field colums is name is "Dlr assigned".

I'm sort of a newbie and have a little problem understanding some of
the terms a read in other posts. Still don't know what DAO, stands
for.

[Steve Jorgensen]

My guess is that your query uses a reference to a form control as a parameter.
This works fine when you use run the query manually or use it as a
recordsource or rowsource, but when you run the query from code via DAO calls
as you are doing here, all parameters must be entered explicilty using the
..Parameters collection of the querydef prior to executing .OpenRecordset.

On 7 Jan 2004 22:53:04 -0800, wo********@comc ast.net (Dee) wrote:

Running an AfterUpdate event procedure, I get the following error:

"Too few parameters. Expected 1."

My code is as follows:
Private Sub DealerID_AfterU pdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("Q rCheckDlrAssign ed")
Set rs = qd.OpenRecordse t()

If Not rs.EOF Then
Me!Terassigned = rs!Dlr_assigned
End If

On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

End Sub
The query returns only one field from one record of a table with 150
records. The field is a yes/no check field.

The query is a select query called "QrCheckDlrAssi gned".
The field colums is name is "Dlr assigned".

I'm sort of a newbie and have a little problem understanding some of
the terms a read in other posts. Still don't know what DAO, stands
for.

[me here]

Dee wrote:

Running an AfterUpdate event procedure, I get the following error:

"Too few parameters. Expected 1."

My code is as follows:
Private Sub DealerID_AfterU pdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("Q rCheckDlrAssign ed")
Set rs = qd.OpenRecordse t()

If Not rs.EOF Then
Me!Terassigned = rs!Dlr_assigned
End If

On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

End Sub
The query returns only one field from one record of a table with 150
records. The field is a yes/no check field.

The query is a select query called "QrCheckDlrAssi gned".
The field colums is name is "Dlr assigned".

I'm sort of a newbie and have a little problem understanding some of
the terms a read in other posts. Still don't know what DAO, stands
for.

I had this problem just 2 days ago. Exactly the same situation. Check
the query QrCheckDlrAssig ned and there will be a criteria referring to
another field or form field ([forms]![myform]![id_field]). In my case
the query I was referring to had no criteria but the query it was based
on had a criteria. I had to delete the parameter 2 queries deep and it
ran straight away. I was really confused as the query would run normally.

Just try removing the criteria in the criteria and seeing if the code runs.

I have used some workarounds to eliminate this problem in the past. Let
me know and I will send them if you want. It involves reading the query
SQL into a string and adding the criteria by string manipulation.

If the query is used for nothing else you could put a token in the
criteria, say 123456, and then replace that token with the value you
have stored in a VBA variable. Have a look at http://www.mvps.org/access
in the Strings section for the Find and Replace function. Very useful.
Pass in three variables, the string being searched, the value to be
replaced and the value to replace it with.

HTH
Gavin

[Edward]

wo********@comc ast.net (Dee) wrote in message news:<35******* *************** ****@posting.go ogle.com>...

Running an AfterUpdate event procedure, I get the following error:

"Too few parameters. Expected 1."

My code is as follows:
Private Sub DealerID_AfterU pdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("Q rCheckDlrAssign ed")
Set rs = qd.OpenRecordse t()

If Not rs.EOF Then
Me!Terassigned = rs!Dlr_assigned
End If

On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

End Sub
The query returns only one field from one record of a table with 150
records. The field is a yes/no check field.

The query is a select query called "QrCheckDlrAssi gned".
The field colums is name is "Dlr assigned".

I'm sort of a newbie and have a little problem understanding some of
the terms a read in other posts. Still don't know what DAO, stands
for.

First off, could you post the SQL for QrCheckDlrAssig ned?

Second, if the column name is Dlr assigned (no underscore) then
rs!Dlr_assigned will fail, because the names are different.

It is generally a Bad Thing to have spaces in column names. Use
PascalCase to name them - DlrAssigned. If you are stuck with that
column name you will need to replace:

rs!Dlr_assigned

with

rs![Dlr assigned]

HTH

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[PC Datasheet]

Dee,

Steve Jorgensen was correct!

Here's how you apply what he said ---

Dim db As DAO.Database
Dim qd As DAO.QueryDef

Dim Param As Parameter '<New>

Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("Q rCheckDlrAssign ed")

' ------------New------------
For each Param in QD.Parameters
Param.Value = Eval(Param.Name )
Next Param
' -----------------------------

Set rs = qd.OpenRecordse t()

If Not rs.EOF Then
Me!Terassigned = rs!Dlr_assigned
End If

On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing



"Dee" <wo********@com cast.net> wrote in message
news:35******** *************** ***@posting.goo gle.com...

Running an AfterUpdate event procedure, I get the following error:

"Too few parameters. Expected 1."

My code is as follows:
Private Sub DealerID_AfterU pdate()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("Q rCheckDlrAssign ed")
Set rs = qd.OpenRecordse t()

If Not rs.EOF Then
Me!Terassigned = rs!Dlr_assigned
End If

On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

End Sub
The query returns only one field from one record of a table with 150
records. The field is a yes/no check field.

The query is a select query called "QrCheckDlrAssi gned".
The field colums is name is "Dlr assigned".

I'm sort of a newbie and have a little problem understanding some of
the terms a read in other posts. Still don't know what DAO, stands
for.

[Dee Simon]

Still doesn't work.

"For Each Param In qd.Parameters" generates a type mismatch error.

By the way I changed the "Dlr assigned" field name to "Dlr_assign ed".

Below is the sql for the query.

SELECT dealers5.Dlr_as signed
FROM dealers5
WHERE
(((dealers5.Dlr Id)=[Forms]![FmDealer5]![TerritoriesDesc ription].[Form]![
DealerID]));

The
")=[Forms]![FmDealer5]![TerritoriesDesc ription].[Form]![DealerID]))"
criteria is where I pass on the numeric long integer value I just
entered on one record of subform being displayed in datasheet view.

What I'm trying to do, is to check if the corresponding dealer is a
valid assigned dealer or just someone who is under consideration but has
not yet been approved.

Thanks for all the interest in helping me to try to solve this problem.

Dee
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Jorgensen]

On 08 Jan 2004 22:39:38 GMT, Dee Simon <wo********@com cast.net> wrote:

Still doesn't work.

"For Each Param In qd.Parameters" generates a type mismatch error.

It probably was using the ADO implementation of Parameter. Try Dim Parameter
as DAO.Parameter. It's always best to specify ADO or DAO for -all-
declarations since there is so much overlap of class names between the two.

[Dee Simon]

Yes that was it.

Thank you, Thank you, Thank you, Thank you, !!!!!

Thanks Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!