Here are a couple of codes for passing parameter values to a query.
Q:Michael O’neill Johnston wrote to ask how to pass a variable (created from a value in a table field) to a parameter query.
A:There are many ways of passing a parameter value to a query. If you are looking for a code equivalent of specifying criteria, the method I like best is to create (or recreate) the query from a SQL statement, using the variable. Here is a typical example:
…
Set dbs = CurrentDb
strQuery = “qryExport” & strSet & “Set”
strSQL = “SELECT Set, Module FROM tblSubjectSets” _
& ” WHERE Set = ” & Chr$(39) & strSet & Chr$(39) & “;”
Debug.Print “SQL String: ” & strSQL
Call DeleteAndRecreateQuery(strQuery, strSQL)
Set rstExport = dbs.OpenRecordset(strQuery)
…
Sub DeleteAndRecreateQuery(strQuery As String, strSQL As String)
On Error Resume Next
‘Delete old query
Set dbs = CurrentDb
dbs.QueryDefs.Delete strQuery
On Error GoTo ErrorHandler
‘Create new query
Set qdf = dbs.CreateQueryDef(strQuery, strSQL)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
I put the code that does the recreation into a separate procedure, for reusability.
If you are talking about the type of parameter query that actually uses parameters (Parameter objects), then here is some sample code from my book on the DAO Object model:
Private Sub cmdParameters_Click()
On Error GoTo ErrorHandler
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prmBegin As DAO.Parameter
Dim prmEnd As DAO.Parameter
Dim dteFirst As Date
Dim dteLast As Date
Dim rst As DAO.Recordset
Set dbs = OpenDatabase(“D:DocumentsNorthwind.mdb”)
Set qdf = dbs.CreateQueryDef(“”, “PARAMETERS dteStartDate DateTime, ” _
& “dteEndDate DateTime; SELECT [FirstName] & ‘ ‘ & [LastName] ” _
& “AS EmployeeName, Orders.OrderID, Orders.OrderDate ” _
& “FROM Orders INNER JOIN Employees ON Orders.EmployeeID = ” _
& “Employees.EmployeeID WHERE Orders.OrderDate Between ” _
& “[dteStartDate] And [dteEndDate] ORDER BY Orders.OrderDate;”)
Set prmBegin = qdf.Parameters!dteStartDate
Set prmEnd = qdf.Parameters!dteEndDate
dteFirst = CDate(InputBox(“Start date”))
dteLast = CDate(InputBox(“End date”))
‘Feed new parameter values to recordset and print
‘values to the Debug window
prmBegin = dteFirst
prmEnd = dteLast
Set rst = qdf.OpenRecordset(dbOpenForwardOnly)
Debug.Print “Printing records from ” & prmBegin & ” to ” & prmEnd
Do While Not rst.EOF
Debug.Print “Employee: ” & rst!EmployeeName
Debug.Print “Order date: ” & rst!OrderDate
rst.MoveNext
Loop
rst.Close
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub