Access Cookbook - Ken Getz [35]
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.
Chapter 2. Forms
As far as users of your applications are concerned, your forms are the application. The forms are the windows into the data that makes Access applications work. Access forms are incredibly flexible and can take on as many different personalities as there are Access developers. The tricks and techniques covered in this chapter are not as complex as ones you might find in other chapters of this book, but they will help form the foundation of your entire application. You'll want to use these tips to help give a consistent look to your forms and to help users find exactly which control currently has the focus. You'll also use them to control where users go on your forms by restricting their movement so they can't move to a new row until you allow them to and by giving your forms custom navigation controls. Your