How can I use Query with Parameters in VBA? Thanks

[Ken Snell \(MVP\)]

Here is a generic way to do it via DAO recordset:

Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("NameOfQueryWith Parameter(s)")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
' rst object contains the query's records after applying the parameters
' code goes here to use the records' data from rst object
'
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

 

[gdct]

Thank you so much!

 

[Marshall Barton]

You can expand on your question's details by using the body
of the message.

The general idea of opening a recordset on a parameter query
is along these lines:

Set db = CurrentDb()
Set qdf = db.QueryDefs!nameofquery
qdf.Parameters!nameofparameter = somevalue
. . .
Set rs = qdf.OpenRecordset( . . . )
. . .
rs.Close : Set rs = Nothing
Set qsd = Nothing
Set db = Nothing