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] CHAR; "
' 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