Too few parameters Expected 1 when setting recordset object to a q

[Guest]

I am using Access 2003 on windows XP Professional. I have developed a time
entry program to track employees' attendance and job costs. I am using a form
bound to the payroll table with a subform bound to the details of the time
for that day. I have a 2 saved queries. one returns a list of all employees
active for that day, the other returns all payroll records for that day. I
then compare the record counts of both to determine that all employees active
for that day also have a time entry for that day. The queries have a date
parameter bound to the date field of my open form. I can open these queries
manually or by code, and they function correctly. however, when I try to open
a recordset object on them in code, I get the "Too few parameters Expected 1"
error message.
Can anyone tell me why? and possibly how I can fix this?
Thanks in advance for your comments

 

[Guest]

Make sure you are handling the data types correctly. For instance, if you are
getting a piece of your SQL through a string, make sure you use Quotation
marks (Chr$(34)) to enclose them. You can also try date literals for your
dates (enclose them in #-signs). Both of these things for me have been in the
past the cause of the error you describe.

Have you stepped through the code to look at your SQL statement before you
attempt to open/execute it? If you can post it here, that would give us more
information.

HTH

 

[Marshall Barton]

I am using Access 2003 on windows XP Professional. I have developed a time
entry program to track employees' attendance and job costs. I am using a form
bound to the payroll table with a subform bound to the details of the time
for that day. I have a 2 saved queries. one returns a list of all employees
active for that day, the other returns all payroll records for that day. I
then compare the record counts of both to determine that all employees active
for that day also have a time entry for that day. The queries have a date
parameter bound to the date field of my open form. I can open these queries
manually or by code, and they function correctly. however, when I try to open
a recordset object on them in code, I get the "Too few parameters Expected 1"
error message.
Can anyone tell me why? and possibly how I can fix this?

The big difference is that when you just open the query from
the db window or use the RunSQL method, you are asking
Access to open the recordset for you. As part of that
procedd, Access will resolve the query parameters.

However, when you use DAO (or some other direct to the db
engine interface), Access is not involved, so you must
resolve the parameters before opening the recordset.

Then general code outline is:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs!queryname
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name) 'different for prompts
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

 

[Guest]

Thank you for your comments Tim, I do use the # signs when I build the sql
string from scratch with code, but these are saved query defs already set up
in the database, and they work when opened manually. The only code involved
here is the code that attemps to open a recordset object based on the query
def. Marshall's suggestion about resolving the parameter before opening the
recordset has fixed the problem.
Thanks to both of you for taking time to comment.

 

[Guest]

Your suggestion worked perfectly, Thanks again for your help.

 

[Guest]

I have a wrinkle on this issue...

I have nested stored queries: qry1 has as one of its recordsources qry2;
qry2 has a parameter prm2. I set the parameter:

qdf2.parameters("prm2")=prm_value

....and then run the resolution:

for each prm in qdf2.parameters
prm.value=eval(prm.name)
next

....the code stops with the error "Microsoft Office Access can't find the
name 'prm2' you entered in the expression." I mouse-over "prm.name" and the
tip responds "prm.name='prm2'"; I mouse-over "prm.value" and the tip responds
"prm.value='prm_value'".

I have set explicit references to DAO for querydef, recordset, and parameter
objects, and I have added parentheses to CurrentDb().

What am I missing?