CODEWITHSUNDEEP
vbams-accessdaodata-access-object
jon
jon

Reputation: 92

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?

Upvotes: 0

Views: 3149

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33474

Change the line to

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

Upvotes: 1

thedev
thedev

Reputation: 2906

try something like:

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

Upvotes: 1

Related Questions