Criteria in Query from Code

[EddyLLC]

I am trying to use the following code to open up a recordset of a query from code behind a window. stDocName is the name of the query. The problem is the criteria I am using in the query is not a field value from the form but a variable I set in the code. Is this possible? I don't know how to reference it variable in the criteria field of the query. Any thoughts?

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set qdf = db.QueryDefs(stDocName)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset

 

[PHV]

Something like this ?
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs(stDocName)
qdf.Parameters("name of parameter").Value = yourVariable
Set rs = qdf.OpenRecordset


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EddyLLC]

Thanks for the reply. I am kind of a beginner. What is meant by "name of parameter"? Is it the field name in the query that must match my variable? I tried that but received a item not found in collect error.

 

[PHV]

If you have defined only one parameter in your saved query:
qdf.Parameters(0).Value = yourVariable


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886