Creating Parameter Queries

The Parameters collection of a QueryDef object identifies the parameters for a parameter query. The following example creates a new parameter query and prompts the user to input values for the parameters, then displays the records that match their criteria. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database
Dim qdf As QueryDef, rst As Recordset
Dim strSQL As String, strResults As String

Set dbs = OpenDatabase(strDbPath)
	
' Define SQL string with PARAMETERS clause.
strSQL = "PARAMETERS [Enter Start of Customer Company Name] Text, " & _
	"[Enter the City] Text; " & _
	"SELECT Customers.* " & _
	"FROM Customers WHERE " & _
	"(((Customers.CompanyName) Like " & _
	"[Enter Start of Customer Company Name] & '*') " & _
	"AND ((Customers.City)=[Enter the City]));"

' Create QueryDef object.
Set qdf = dbs.CreateQueryDef("qryCustSearch")
qdf.SQL = strSQL

' Prompt user for parameter values.
qdf.Parameters("[Enter Start of Customer Company Name]") = _
	InputBox("Enter the first few letters of the customer's company name.")
qdf.Parameters("[Enter the City]") = _
	InputBox("Enter the customer's city.")
	
' Open Recordset object on QueryDef object.
Set rst = qdf.OpenRecordset
Do Until rst.EOF
	strResults = strResults & rst!CompanyName & vbCrLf
	rst.MoveNext
Loop

' Show customers returned.
If Not rst.BOF And rst.EOF Then
	MsgBox "Customers matching your criteria: " & vbCrLf & strResults
End If

Unlike a TableDef object, in which you have to explicitly append each Field object to the Fields collection, the Fields and Parameters collections for QueryDef objects are automatically derived by Microsoft Jet based on the contents of the SQL property of the QueryDef object.

See Also For information about using permanent QueryDef objects, including how to supply parameter values programmatically, see Chapter 4, “Queries.”