Pass Parameters to Query from Form Control

[akphidelt]

Hi there, I'm currently in a jam. I created this big long code that works
perfectly until I enter a criteria in the query.

For example I have a form that has a control that is the end date of the
staff that needs to be allocated. So the criteria in the query looks like

Between Forms!Allocation!calStaffEnd - 6 And Forms!Allocation!calStaffEnd

When I put that in the query and run the code I get an error on line

Set rstStaff = MyDB.OpenRecordset("Send To Excel",dbOpenDynaset)

The error is Run-Time error 3061 Too few Parameters. Expected 1

I did some research on it and I've tried setting the Parameters by doing
something like this

Dim qdfStaff as DAO.QueryDef
Set qdfStaff = MyDB.QueryDefs("Send To Excel")

For Each prm In qdfStaff.Parameters
prm.Value = Eval(prm.Name)
Next

But I still get the same error message.
Any one have a clue on what I can do to run the query?

 

[Douglas J. Steele]

How have you declared rstStaff? Make sure it's

Dim rstStaff As DAO.Recordset

 

[akphidelt]

Doug,

Thanks for the response... I took the advice and am still getting the Error.
Here is how it is set up

Dim MyDB as DAO.Database
Dim rstStaff As DAO.Recordset
Dim qdfStaff As DAO.QueryDef

Set qdfStaff = MyDB.QueryDefs("Send To Excel")

For Each prm In qdfStaff.Parameters
prm.Value = Eval(prm.Name)
Next

Set rstStaff = MyDB.OpenRecordset("Send To Excel", dbOpenDynaset)

Is there anything there that you see that might be causing the problem?

Thanks again

 

[akphidelt]

Oh yea,

I also have

Set MyDB = CurrentDb

Doug,

Thanks for the response... I took the advice and am still getting the Error.
Here is how it is set up

Dim MyDB as DAO.Database
Dim rstStaff As DAO.Recordset
Dim qdfStaff As DAO.QueryDef

Set qdfStaff = MyDB.QueryDefs("Send To Excel")

For Each prm In qdfStaff.Parameters
prm.Value = Eval(prm.Name)
Next

Set rstStaff = MyDB.OpenRecordset("Send To Excel", dbOpenDynaset)

Is there anything there that you see that might be causing the problem?

Thanks again

 

[akphidelt]

Hey, I actually think I got it to work...

I changed MyDB.OpenRecordset("Send To Excel", dbOpenDynaset) to

qdfStaff.OpenRecordset(dbOpenDynaset)

I have no clue what the difference is but it worked. Do you know why this
worked?

 

[Douglas J. Steele]

Why it worked is because while you were setting the values of the parameters
through the use of

For Each prm In qdfStaff.Parameters
prm.Value = Eval(prm.Name)
Next

you weren't using that instance of the query in your statement

Set rstStaff = MyDB.OpenRecordset("Send To Excel", dbOpenDynaset)