Dec 20, 2018 - Named arguments are especially useful when you are calling a procedure that has optional arguments. If you use named arguments, you don't.
When you run a parameter query from the user interface, Access canfind the parameters if they have already been satisfied using a formand run the query. When you create a recordset from VBA, however, theJet engine isn’t able to locate the parameter references.Fortunately, you can help the Jet engine find the parameters byopening the QueryDef prior to creating the recordset and telling Jetwhere to look for the parameters.
Open the frmAlbumsPrm form found in
01-16.MDB
.This form, which is similar to a form used in Section 1.1.2 , is usedto collect parameters for a query, qryAlbumsPrm. Select a music typefrom the combo box, enter a range of years in the text boxes, andclick on OK. An event procedure attached to the cmdOK command buttonwill run, making the form invisible but leaving it open. Now runqryAlbumsPrm from the database container. This query, which has threeparameters linked to the now-hidden frmAlbumsPrm, will produce adatasheet limited to the records you specified on the form.Now open the basCreateRst module from
01-16.MDB
.Select the function CreatePrmRst1 from the Procdrop-down list. Its source code is shown here:As you can see, this routine starts by opening the form in dialogmode to collect the three parameters. When the user satisfies theparameters and clicks OK, the form is hidden by an event procedureand control passes back to CreatePrmRst1. Theprocedure then attempts to create a recordset based on the parameterquery and display a message box with the number of records found. Totest this procedure, select View → Debug Window and enter thefollowing in the debug window:
The procedure will fail with error 3061—“Too fewparameters. Expected 3”—at this line:
Now select the function CreatePrmRst2 from theProc drop-down list. This subroutine is the same asCreatePrmRst1, except for some additional codethat satisfies the query’s parameters prior to creating therecordset. Run this version of the subroutine by entering thefollowing in the debug window:
You should now see a dialog reporting the number of records in therecordset.