How can I pass values from a recordset to an SQL clause?

[Georgios Liakopoulos]

Hello,
I am trying to make this code work using ADO in Access:

recordset2.Open "SELECT * FROM Table1 WHERE (((Table1.IDNum) =
(recordset1.IDNum)))", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

* Table1 contains all records of the database
* recordset1 (which already exists) contains IDNums of records that I
want to retrieve from Table1. This recordset was fabricated from scratch
and contains only one field (IDNums)
* recordset2 is the one I want to open: it should contain only those
records from Table1 that their IDNum is included in recordset1

I would appreciate any help

 

[Carl Rapson]

Hello,
I am trying to make this code work using ADO in Access:

recordset2.Open "SELECT * FROM Table1 WHERE (((Table1.IDNum) =
(recordset1.IDNum)))", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

* Table1 contains all records of the database
* recordset1 (which already exists) contains IDNums of records that I want
to retrieve from Table1. This recordset was fabricated from scratch and
contains only one field (IDNums)
* recordset2 is the one I want to open: it should contain only those
records from Table1 that their IDNum is included in recordset1

I would appreciate any help

You need to concatenate the value from recordset1:

recordset2.Open "SELECT * FROM Table1 WHERE (((Table1.IDNum) = (" &
recordset1.IDNum & ")))", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic


Carl Rapson

 

[Georgios Liakopoulos]

You need to concatenate the value from recordset1:

recordset2.Open "SELECT * FROM Table1 WHERE (((Table1.IDNum) = (" &
recordset1.IDNum & ")))", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic


Carl Rapson

Thank you Carl. You are right concerning the syntax. I did that and
works. The problem though is that I only get the record (the one that
has the same IDNum as the last IDNum of the recordset1); What I would
like to do is to get ALL the records of Table1 whose IDNum can be found
in recordset1. Here is the code I wrote in the meantime:

recordset2.Open "SELECT * FROM Table1 & _
WHERE Table1.IDNum = " & recordset1.Fields(0).Value,
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Alternatively, I could code as follows to get all the records:

-----------------------------------------------------------------------
recordset1.MoveFirst

For i=1 to recordset1.recordcount-1
IDNum(i) = recordset1.Fields(0).Value
mystring = mystring & IDNum(i) & "OR "
recordset1.MoveNext

Next

recordset2.Open "SELECT * FROM Table1 & _
WHERE (((Table1.IDNum) = (" & mystring & ")))",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
-----------------------------------------------------------------------

However, I thing that this is not a good programming practice.
Ideally, I would like to be able to make something like a query by
INNERJOINNING the IDNum of Table1 to that of recordset1, but I don't
think that this is possible, right?

 

[Carl Rapson]

Thank you Carl. You are right concerning the syntax. I did that and works.
The problem though is that I only get the record (the one that has the
same IDNum as the last IDNum of the recordset1); What I would like to do
is to get ALL the records of Table1 whose IDNum can be found in
recordset1. Here is the code I wrote in the meantime:

recordset2.Open "SELECT * FROM Table1 & _
WHERE Table1.IDNum = " & recordset1.Fields(0).Value,
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Alternatively, I could code as follows to get all the records:

-----------------------------------------------------------------------
recordset1.MoveFirst

For i=1 to recordset1.recordcount-1
IDNum(i) = recordset1.Fields(0).Value
mystring = mystring & IDNum(i) & "OR "
recordset1.MoveNext

Next

recordset2.Open "SELECT * FROM Table1 & _
WHERE (((Table1.IDNum) = (" & mystring & ")))", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
-----------------------------------------------------------------------

However, I thing that this is not a good programming practice.
Ideally, I would like to be able to make something like a query by
INNERJOINNING the IDNum of Table1 to that of recordset1, but I don't think
that this is possible, right?

What I would do is use a subquery, embedding the same query that was used to
create recordset1:

recordset2.Open "SELECT * FROM Table1 WHERE IDNum IN (SELECT ...)"


Carl Rapson