Access Cookbook

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-16.MDB. This form, which is similar to a form used in Section 1.1.2 , 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. ...