Open Parameter query in code

[Joel]

TIA:

Have code to open query as
rst...rst=currentdb.openrecordset ("queryname")
the query has 2 parameters (parameters are fields on open
form). Error when run code about not inputting parameters.

How do I do this?

A docmd.openquery works but I want to do testing in code.

Thanks,

Joel

 

[John Vinson]

TIA:

Have code to open query as
rst...rst=currentdb.openrecordset ("queryname")
the query has 2 parameters (parameters are fields on open
form). Error when run code about not inputting parameters.

How do I do this?

A docmd.openquery works but I want to do testing in code.

Use the Querydef object, which has a Parameters collection:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset
Dim prm As Parameter
Set db = CurrentDb
Set qd = db.Querydefs("queryname")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name) ' or explicitly set to desired value
Next prm
Set rs = qd.OpenRecordset
<do something with the data>
rs.Close
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

 

[Marshall Barton]

Have code to open query as
rst...rst=currentdb.openrecordset ("queryname")
the query has 2 parameters (parameters are fields on open
form). Error when run code about not inputting parameters.

Access consists of several environments and the VBA
environment does not resolve query parametes automatically.
One way to deal with this is:

Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs!queryname
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Parameters(1).Value = Eval(qdf.Parameters(1).Name)
Set rst = qdf.OpenRecordset(dbOpenDynaset)
. . .
rst.Close : Set rst = Nothing
Set qdf = Nothing
Set db = Nothing