You want to create a recordset while inside some VBA code.

The method I use frequently is this:

(article continues after sponsor spot)

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?

Create the actual query and test it thoroughly. Add and test parameters if needed. Create some VBA code like the example below.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Dim qdf As QueryDef
Dim rst as Recordset
Dim param As Parameter
Set qdf = db.QueryDefs("qryXXXXX")
'--- needed to satisfy any parameters in the query
'--- the for loop is NOT needed if there are no parameters in the query For Each param In qdf.Parameters
    param.value = Eval(param.Name)
Next param
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Continue in your VBA code as you normally would with a recordset object. To have the parameters supplied by your form rather than being prompted for them one by one, see the article via this link.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy