Query on a Recordset

[Andy]

I was hoping to create a recordset once based on a query and then create a
subsequent recordset (based on a subset of the first recordset) from within a
recursive loop so that I did not have to retrieve the data from the database
each time within the loop.

Any ideas on how to effectively filter the original recordset into another
recordset?

Thanks.

 

[Amy Blankenship]

Go into your query designer and create query 1. Then, open a new query.
When it opens the dialogue where you can select tables to show, go to the
Query tab and select query 1.

Now select your fields, etc.

HTH;

Amy

 

[Marshall Barton]

I was hoping to create a recordset once based on a query and then create a
subsequent recordset (based on a subset of the first recordset) from within a
recursive loop so that I did not have to retrieve the data from the database
each time within the loop.

Any ideas on how to effectively filter the original recordset into another
recordset?

You can use a recordset's Filter property to specify the
subset:

Dim rs as DAO.Recordset
Dim rssub As DAO.Recordset
Set rs = db.OpenRecordset( . . .)

For Each X In something
rs.Filter = "somefield = " & somevalue
Set rssub = rs.OpenRecordset()
'do your thing
rssub.Close : Set rssub = Nothing
Next X
rs.Close : Set rs = Nothing

 

[Brendan Reynolds]

You don't mention whether you're using ADO or DAO, but in both object models
the Recordset object has a Filter property ...

Using the Employees table from the Northwind sample database ...

Public Sub TestFilter()

Dim db As DAO.Database
Dim rstd As DAO.Recordset
Dim rsta As ADODB.Recordset

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT * FROM Employees")
Set rsta = New ADODB.Recordset
rsta.Open "SELECT * FROM Employees", CurrentProject.Connection

rstd.Filter = "LastName = 'Davolio'"
Debug.Print rstd.Fields("FirstName")

rsta.Filter = "LastName = 'Davolio'"
Debug.Print rsta.Fields("FirstName")

rstd.Close
rsta.Close


End Sub

Result in the Immediate window ...

testfilter
Nancy
Nancy