[SOLVED] How do I open an Access recordset using a value from another recordset

How do I open an Access recordset using a value from another recordset

This should be obvious, but I am stumped.

I am in Access 2007, and I am looping through records. I want to filter the second recordset on the first.

The code is as follows:

Dim db as Database
Dim rst1 as DAO.Recordset, rst2 as DAO.Recordset
Set rst1 = db.OpenRecordset("TABLE1", dbOpenDynaset)

rst1.MoveFirst
Do Until rst1.EOF
    rst1.Edit
    set rst2 = db.OpenRecordset("SELECT * FROM TABLE2 WHERE ID = 'rst1![ID]';")
    ....

This does not open a recordset filtered on ID. However, the following code runs without error (where ID = 0001) and I get an appropriately filtered recordset.

set rst2 = db.OpenRecordset("SELECT * FROM TABLE2 WHERE ID = '0001';")

I have tested to make sure that rst1![ID] = 0001. I have placed 0001 in a variable and placed the variable in the statement, also without luck. I did notice that using the rst("ID") syntax causes an immediate error. I also tried building the SQL in the query editor and copying and pasting.

What am I missing?

Solution

try something like:

sSql = "SELECT * FROM TABLE2 WHERE ID = '" & rst1!ID & "'" 
set rst2 = db.OpenRecordset(sSql)