the Manx Man's MS Access Pages

Creating a Recordset from an existing Query (002)

You want to create a recordset while inside some VBA code. The method I use frequently is this:

set rst = db.openrecordset("select * from table where field='abc'")

That works fine for simple relationships, especially like the example, which only has a single table involved.

What if you have a very complex query? I've had ones where I ran out of continuation lines! Why not just create a query using the query wizard and then use that as a basis for the recordset?

Here's how.

Dim qdf As QueryDef
Dim param As Parameter

Set qdf = db.QueryDefs("qryXXXXX")
'--- needed to satisfy any parameters in the query
For Each param In qdf.Parameters
       param.value = Eval(param.Name)
Next param
Set rst = qdf.OpenRecordset(dbOpenDynaset)

To have the parameters supplied by text boxes on a form, see this tip.