Parameter Object, Parameters Collection Example

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