PARAMETERS Declaration Example

This example requires the user to provide a job title and then uses that job title as the criteria for the query.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub ParametersX()

   Dim dbs As Database, qdf As QueryDef
   Dim rst As Recordset
   Dim strSql As String, strParm As String
   Dim strMessage As String
   Dim intCommand As Integer
   
   ' Modify this line to include the path to Northwind
   ' on your computer.
   Set dbs = OpenDatabase("NorthWind.mdb")
   
   ' Define the parameters clause.
   strParm = "PARAMETERS [Employee Title] TEXT; "

   ' Define an SQL statement with the parameters
   ' clause.
   strSql = strParm & "SELECT LastName, FirstName, " _
      & "EmployeeID " _
      & "FROM Employees " _
      & "WHERE Title =[Employee Title];"
   
   ' Create a QueryDef object based on the 
   ' SQL statement.
   Set qdf = dbs.CreateQueryDef _
      ("Find Employees", strSql)
   
   Do While True
      strMessage = "Find Employees by Job " _
         & "title:" & Chr(13) _
         & "  Choose Job Title:" & Chr(13) _
         & " 1 - Sales Manager" & Chr(13) _
         & " 2 - Sales Representative" & Chr(13) _
         & " 3 - Inside Sales Coordinator"
      
      intCommand = Val(InputBox(strMessage))
      
      Select Case intCommand
         Case 1
            qdf("Employee Title") = _
               "Sales Manager"
         Case 2
            qdf("Employee Title") = _
               "Sales Representative"
         Case 3
            qdf("Employee Title") = _
               "Inside Sales Coordinator"
         Case Else
            Exit Do
      End Select
      
      ' Create a temporary snapshot-type Recordset.
      Set rst = qdf.OpenRecordset(dbOpenSnapshot)
      ' Populate the Recordset.
      rst.MoveLast
         
   ' Call EnumFields to print the contents of the 
   ' Recordset. Pass the Recordset object and desired
   ' field width.
      EnumFields rst, 12
   Loop
   
   ' Delete the QueryDef because this is a
   ' demonstration.
   dbs.QueryDefs.Delete "Find Employees"
   
   dbs.Close

End Sub