Open Parameter query in code

  • Thread starter Thread starter Joel
  • Start date Start date
J

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
 
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.
Click to expand...

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
 
Joel said:
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.
Click to expand...

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
 
Back
Top