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.”