Parameter Object

Description

A Parameter object represents a parameter associated with a QueryDef object created from a parameter query.

Remarks

Using the properties of a Parameter object, you can set a query parameter that can be changed before the query is run. You can:

Check the Name property setting to determine the name of a parameter.
Check or set the Value property (default) of a parameter. For example:

qdfNewHires.Parameters(0) = 5

Check the Type property setting to determine the data type of the Parameter object.

The Parameters collection provides information only about existing parameters. You can't append objects to, or delete objects from, the Parameters collection.

The only method supported by the Parameters collection is the Refresh method.

Properties

Name Property, Type Property, Value Property.

See Also

Appendix, "Data Access Object Hierarchy."

Example

This example sets two query parameters for a hypothetical parameter query named "ParamQuery," executes the query by opening a Recordset from the QueryDef, and then prints the properties of each parameter. Note that this query does not actually exist in the Northwind sample database. The parameter data types are of type Date.

Dim dbsCurrent As Database
Dim qdfParam As QueryDef, prmEnum As Parameter
Dim rstParam As Recordset
Dim X As Integer
Set dbsCurrent = DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set qdfParam = dbsCurrent.QueryDefs("ParamQuery")    ' Open existing 
                                                    ' QueryDef.
qdfParam.Parameters("Order Date") = "10/11/94"        ' Set parameters.
qdfParam.Parameters("Ship Date") = "11/4/94"
Set rstParam = qdfParam.OpenRecordset()                ' Open Recordset.
For X = 0 To qdfParam.Parameters.Count - 1
    Set prmEnum = qdfParam.Parameters(X)
    Debug.Print prmEnum.Name            ' Print parameter properties.
    Debug.Print prmEnum.Type
    Debug.Print prmEnum.Value
Next X
rstParam.Close

Example (Microsoft Access)

The following example creates a new parameter query and supplies values for the parameters.

Sub NewParameterQuery()
    Dim dbs As Database, qdf As QueryDef, rst As Recordset
    Dim prm As Parameter, strSQL As String

    ' Return Database object pointing to current database.
    Set dbs = CurrentDb
    ' Construct SQL string.
    strSQL = "PARAMETERS [Beginning OrderDate] DateTime, " & _
        "[Ending OrderDate] DateTime; SELECT * FROM Orders " & _
        "WHERE ([OrderDate] Between[Beginning OrderDate] " & _
        "And [Ending OrderDate]);"
    ' Create new QueryDef object.
    Set qdf = dbs.CreateQueryDef("ParameterQuery", strSQL)
    ' Supply values for parameters.
    qdf.Parameters![Beginning OrderDate] = #4-1-95#
    qdf.Parameters![Ending OrderDate] = #4-30-95#
End Sub