Parameters Collection (DAO)
Last modified: July 01, 2011
Applies to: Access 2013 | Office 2013
In this article
Remarks
Example
About the Contributors
A Parameters collection contains all the Parameter objects of a QueryDef object.
This example demonstrates Parameter objects and the Parameters collection by creating a temporary QueryDef and retrieving data based on changes made to the QueryDef object's Parameters. The ParametersChange procedure is required for this procedure to run.
Sub ParameterX()
Dim dbsNorthwind As Database
Dim qdfReport As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
' Create temporary QueryDef object with two
' parameters.
Set qdfReport = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
"SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
"FROM Orders WHERE ShippedDate BETWEEN " & _
"[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
"ORDER BY EmployeeID")
Set prmBegin = qdfReport.Parameters!dteBegin
Set prmEnd = qdfReport.Parameters!dteEnd
' Print report using specified parameter values.
ParametersChange qdfReport, prmBegin, #1/1/95#, _
prmEnd, #6/30/95#
ParametersChange qdfReport, prmBegin, #7/1/95#, _
prmEnd, #12/31/95#
dbsNorthwind.Close
End Sub
Sub ParametersChange(qdfTemp As QueryDef, _
prmFirst As Parameter, dteFirst As Date, _
prmLast As Parameter, dteLast As Date)
' Report function for ParameterX.
Dim rstTemp As Recordset
Dim fldLoop As Field
' Set parameter values and open recordset from
' temporary QueryDef object.
prmFirst = dteFirst
prmLast = dteLast
Set rstTemp = _
qdfTemp.OpenRecordset(dbOpenForwardOnly)
Debug.Print "Period " & dteFirst & " to " & dteLast
' Enumerate recordset.
Do While Not rstTemp.EOF
' Enumerate Fields collection of recordset.
For Each fldLoop In rstTemp.Fields
Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
Next fldLoop
Debug.Print
rstTemp.MoveNext
Loop
rstTemp.Close
End Sub
The following example shows how to create a parameter query. A query named myQuery is created with two parameters, named Param1 and Param2. To do this, the SQL property of the query is set to a Structured Query Language (SQL) statement that defines the parameters.
Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors
Sub CreateQueryWithParameters()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("myQuery")
Application.RefreshDatabaseWindow
strSQL = "PARAMETERS Param1 TEXT, Param2 INT; "
strSQL = strSQL & "SELECT * FROM [Table1] "
strSQL = strSQL & "WHERE [Field1] = [Param1] AND [Field2] = [Param2];"
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
Set dbs = Nothing
End Sub
The following example shows how to execute a parameter query. The Parameters collection is used to set the Organization parameter of the myActionQuery query before the query is executed.
Public Sub ExecParameterQuery()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("myActionQuery")
'Set the value of the QueryDef's parameter
qdf.Parameters("Organization").Value = "Microsoft"
'Execute the query
qdf.Execute dbFailOnError
'Clean up
qdf.Close
Set qdf = Nothing
Set dbs = Nothing
End Sub
The following example shows how to open a Recordset that is based on a parameter query.
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb
'Get the parameter query
Set qfd = dbs.QueryDefs("qryMyParameterQuery")
'Supply the parameter value
qdf.Parameters("EnterStartDate") = Date
qdf.Parameters("EnterEndDate") = Date + 7
'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()