Recipe 1.17 Create a Recordset Based on a Parameter Query from VBA Code

1.17.1 Problem

You have a parameter query that is linked to a form by three parameters. When you open the form, enter the information into the form's controls to satisfy the parameters, and then run the query interactively, everything is fine. But when you open the form, satisfy the parameters, and create a recordset from VBA code based on the same query, you get an error message complaining that no parameters were supplied. This doesn't make sense, since you've already supplied the parameters on the form. Is there any way to create a recordset from VBA based on a parameter query?

1.17.2 Solution

When you run a parameter query from the user interface, Access can find the parameters if they have already been satisfied using a form and run the query. When you create a recordset from VBA, however, the Jet engine isn't able to locate the parameter references. Fortunately, you can help the Jet engine find the parameters by opening the QueryDef prior to creating the recordset and telling Jet where to look for the parameters.

Open the frmAlbumsPrm form found in 01-17.MDB. This form, which is similar to a form used in the Solution in Recipe 1.1, is used to collect parameters for a query, qryAlbumsPrm. Select a music type from the combo box, enter a range of years in the text boxes, and click on OK. An event procedure attached to the cmdOK command button will run, making the form invisible but leaving it open. Now run qryAlbumsPrm from the database container. This query, which has three parameters linked to the now-hidden frmAlbumsPrm, will produce a datasheet limited to the records you specified on the form.

Now open the basCreateRst module from 01-17.MDB. Select the function CreatePrmRst1 from the Proc drop-down list. Its source code is shown here:

Public Sub CreatePrmRst1( )
    
    ' Example of creating a recordset based on a parameter query.
    ' This example fails!

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb( )
    
    ' Open the form to collect the parameters.
    DoCmd.OpenForm "frmAlbumsPrm", , , , , acDialog
    
    ' OK was pressed, so create the recordset.
    If IsFormOpen("frmAlbumsPrm") Then
        
        ' Attempt to create the recordset.
        Set rst = db.OpenRecordset("qryAlbumsPrm")
        
        rst.MoveLast
        
        MsgBox "Recordset created with " & rst.RecordCount & _
         " records.", vbOKOnly + vbInformation, "CreatePrmRst"
        
        rst.Close
    Else
        ' Cancel was pressed.
        MsgBox "Query canceled!", vbOKOnly + vbCritical, _
         "CreatePrmRst"
    End If
    
    DoCmd.Close acForm, "frmAlbumsPrm"
    Set rst = Nothing
    Set db = Nothing
End Sub

As you can see, this routine starts by opening the form in dialog mode to collect the three parameters. When the user satisfies the parameters and clicks OK, the form is hidden by an event procedure and control passes back to CreatePrmRst1. The procedure then attempts to create a recordset based on the parameter query and display a message box with the number of records found. To test this procedure, select View Debug Window and enter the following in the debug window:

Call CreatePrmRst1

The procedure will fail with error 3061—"Too few parameters. Expected 3"—at this line:

Set rst = db.OpenRecordset("qryAlbumsPrm")

Now select the function CreatePrmRst2 from the Proc drop-down list. This subroutine is the same as CreatePrmRst1, except for some additional code that satisfies the query's parameters prior to creating the recordset. Run this version of the subroutine by entering the following in the debug window:

Call CreatePrmRst2

You should now see a dialog reporting the number of records in the recordset.

1.17.3 Discussion

The VBA code for the second version of the routine, CreatePrmRst2, is shown here:

Sub CreatePrmRst2( )

    ' Example of creating a recordset based on a parameter query.
    ' This example succeeds!

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb( )
    
    ' Open the form to collect the parameters.
    DoCmd.OpenForm "frmAlbumsPrm", , , , , acDialog
    
    ' OK was pressed, so create the recordset.
    If IsFormOpen("frmAlbumsPrm") Then
        
        ' Satisfy the three parameters before attempting to create a recordset.
        Set qdf = db.QueryDefs("qryAlbumsPrm")
        
        qdf("Forms!frmAlbumsPrm!cboMusicType") = Forms!frmAlbumsPrm!cboMusicType
        qdf("Forms!frmAlbumsPrm!txtYear1") = Forms!frmAlbumsPrm!txtYear1
        qdf("Forms!frmAlbumsPrm!txtYear2") = Forms!frmAlbumsPrm!txtYear2
        
        ' Attempt to create the recordset.
        Set rst = qdf.OpenRecordset( )
        
        rst.MoveLast
        
        MsgBox "Recordset created with " & rst.RecordCount & " records.", _
         vbOKOnly + vbInformation, "CreatePrmRst"
        
        qdf.Close
        rst.Close
    Else
        ' Cancel was pressed.
        MsgBox "Query cancelled!", vbOKOnly + vbCritical, "CreatePrmRst"
    End If
    
    DoCmd.Close acForm, "frmAlbumsPrm"
    Set qdf = Nothing
    Set rst = Nothing
    Set db = Nothing
End Sub

The main difference between the two procedures is the inclusion of the following lines of code prior to the line that creates the recordset:

Set qdf = db.QueryDefs("qryAlbumsPrm")

qdf("Forms!frmAlbumsPrm!cboMusicType") = Forms!frmAlbumsPrm!cboMusicType
qdf("Forms!frmAlbumsPrm!txtYear1") = Forms!frmAlbumsPrm!txtYear1
qdf("Forms!frmAlbumsPrm!txtYear2") = Forms!frmAlbumsPrm!txtYear2

The extra code opens the parameter QueryDef and then sets each of its parameters equal to its current value. You do this using the following syntax:

qdf("Parameter") = Parameter

Then the recordset is created based on the opened QueryDef:

Set rst = qdf.OpenRecordset( )

This time the recordset is created without a problem because you supplied the parameters prior to executing the OpenRecordset method.

You can also use this technique to satisfy parameters using VBA variables, instead of actually going to the form. For example, if you collected the parameters for qryAlbumPrm and stored them in three variables—varMusicType, varYear1, and varYear2--you could open the QueryDef and create the recordset using the following code:

Set qdf = db.QueryDefs("qryAlbumsPrm")

qdf("Forms!frmAlbumsPrm!cboMusicType") = varMusicType
qdf("Forms!frmAlbumsPrm!txtYear1") = varYear1
qdf("Forms!frmAlbumsPrm!txtYear2") = varYear2

Set rst = qdf.OpenRecordset( )

The advantage of using this approach instead of the one demonstrated in the Solution in Recipe 1.7, which uses a function to satisfy a parameter, is that this technique allows you to use the same parameter query and run it either interactively or from VBA code.

If you know that all your parameters are references to controls on forms, and if you do want to get the values from the forms, you can use a generic shortcut for filling in the parameter values. Thus, instead of hardcoding the parameter names, you could do this:

Dim prm as DAO.Parameter


For Each prm in qdf.Parameters
   prm.Value = Eval(prm.Name)
Next prm

If you feed a control reference to the Access Eval function, it will give you back the value contained in the control.