Using Parameter Queries

In many situations, you’ll want a user or another program to provide parameters to your stored queries and Recordset objects. Microsoft Jet provides the means to do this. You first create a QueryDef object, specifying which parameters are to be provided by the end user. When you open a Recordset object based on the QueryDef object, the application opens a dialog box that prompts the user to enter a value, such as the criteria for a WHERE clause or the field on which to sort the selected records.

The following example creates a temporary query that returns the name and hire date of each employee hired after a certain date. Before running the query, the program calls the InputBox function to prompt the user for a threshold date. The names are then shown in the Debug window, starting with the most recent hire. In this example, strDbPath is the path to the NorthwindTables database:

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

Set dbs = OpenDatabase(strDbPath)
' Create SQL statement with parameters.
strSQL = "PARAMETERS BeginningDate DateTime; " & _
	"SELECT FirstName, LastName, HireDate FROM Employees " & _
	"WHERE HireDate >= BeginningDate " & _
	"ORDER BY HireDate ASC;"
' Create temporary QueryDef.
Set qdf = dbs.CreateQueryDef("", strSQL)
' Prompt user for input.
strInput = InputBox("Enter the earliest hire date:")
If Len(strInput) = 0 Then Exit Sub
If Not IsDate(strInput) Then Exit Sub
' Set parameter value.
qdf.Parameters("BeginningDate") = strInput
' Open recordset.
Set rst = qdf.OpenRecordset()
With rst
	If .BOF Then
		MsgBox "No employees hired after date: " & strInput
		Exit Sub
	End If
	Do While Not .EOF
		Debug.Print !FirstName & " " & !LastName & " was hired on: " & !HireDate
		.MoveNext
	Loop
	.Close
End With

Most of the database maintenance tasks described in the rest of this chapter can be accomplished by using stored parameter queries.