Opening parameter queries in code (Access97/SP2)

Topic

New Reply

How do you open parameter query as a recordset?

I am trying to set a query which gets its parameters from a search form where users can type in required values. When I either open the query manually or by using DoCmd.OpenQuery (“qryTestingCriteria”) it opens fine. However, when I try to open a recordset based on this query by using OpenRecordSet statement it gives me the following error:

===========================

Reply | Quote

Replies

You can’t do it using DoCmd.OpenQuery. One way to do it using DAO (your second example) is like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = dbs.QueryDefs(“qryTestingCriteria”)
Set prm = qdf.Parameters(0)
prm = InputBox(strName, “Enter parameter value”)
Set rst = qdf.OpenRecordset

… DO STUFF

rst.Close
Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
set db = nothing

This is simplistic, of course, and assumes that you have one parameter that is not a string or a date, but it will give you the general approach.

Reply | Quote

You can fill in all your parameters using the Eval() function before opening the recordset. See code snippet below.

Dim dbs As Database
Dim qdf As QueryDef
Dim prm As Parameter
Dim rstGraphData As Recordset
‘ Return reference to current database.
Set dbs = CurrentDb
‘ Open dynaset-type Recordset object.
Set qdf = dbs.QueryDefs(“qryGraph”)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rstGraphData = qdf.OpenRecordset(dbOpenDynaset)

HTH

Peter

Reply | Quote