Passing Parameters to the Data Control - September 1997

At last month’s VB SIG meeting I asked if anyone knew how to pass parameters to the data control. The presenter, Jim Coughlin [vanalen@inow.com], said he would e-mail me a method. His message is displayed below. I have not had a chance to use the method yet.

Larry, the main points are this:

  1. First you must create a parameterized query (using Access or other means).
  2. Reference the individual Parameter of the Parameters collection within the QueryDef within the QueryDefs collection.

In the example enclosed I have an object that stores a reference to the Database object that is obtained through a Property Get. In the following example dcWorkOrder is the name of a DataControl object.

The example uses an attached table to an MSSQL database. Therefore, your options of the OpenRecordset method will most likely be different. The recordset attached to a DataControl is "Opened" at the completion of the Form Load Event unless the .Database and recordSource properties are set to blank. By setting them to blank in the Form Load event you can defer "opening" the recordset until later.

Be aware that Bound controls such as grids that bind themselves to Parameterized queries do not like them at design time (because during design time there is no way to prompt the user for the parameters). To get around this create a duplicate query for design purposes and assign this query name to the RecordSource property using the VB property sheet. Then during runtime do the substitution of the parameterized query.

As a good practice, you should always modify the Database Property of the DataControl at runtime to allow the deployment in a production environment. That is, in a production environment, the place where your .MDB file is located is not the same on every machine or in the same place where it was during the development environment.

Jim

Sample VB Code

Public Sub XOpen(ByVal work_order_id As Variant, ByVal vendor_id As String, ByVal vendor_name As String)
Dim qdef As QueryDef
If IsNull(work_order_id) Then
    'v0226uWorkOrder has criteria: WorkOrderID IS NULL
    'This will create an empty recordset into which new rows can be inserted.
   Set qdef = M000_ProjectDB.DataBaseX.QueryDefs("v026uWorkOrder")
Else
    Set qdef = M000_ProjectDB.DataBaseX.QueryDefs("v021upWorkOrder")
    qdef.Parameters("p_WorkOrderID") = work_order_id
End If
Set dcWorkOrder.Recordset = qdef.OpenRecordset(dbOpenDynaset, dbSeeChanges, DAO.dbOptimistic)
End Sub

Download As Word 97 Document

Click here to download this page as a zipped Word 97 Document [5k].

Previous Tip   Tip Index   Next Tip

Home

Updated 2000/10/29