Access Cookbook - Ken Getz [34]
1.17. Create a Recordset Based on a Parameter Query from VBA Code
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?
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.
Discussion
The VBA code for the second version of the routine, CreatePrmRst2, is shown here: