Pass Parameters to Query from Form Control

  • Thread starter Thread starter akphidelt
  • Start date Start date
A

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?
 
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
 
Oh yea,

I also have

Set MyDB = CurrentDb

akphidelt said:
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
Click to expand...
 
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?
 
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)
 
Back
Top