Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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
variablesvarMusicType,
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.