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 but I get an
error message:

Run-time error '-2147217904 (80040e10)'
'No value given for one or more required parameters'

Here is the code:

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

 

[Douglas J. Steele]

Recordsets know nothing about one another. You need to refer to the value
from recordset1, which means it needs to be outside of the quotes. If IDNum
is numeric, try:

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

If it's text, try:

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

Exagerated for clarity, that second line is

"WHERE Table1.IDNum = ' " & recordset1.IDNum & " ' ", _

 

[Georgios Liakopoulos]

Thank you Douglas. I get the point. In fact I tried this in the
meantime. However, the problem is that I only get only one 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 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

Any ideas?
Thanks again!

 

[Douglas J. Steele]

What are the details of recordset1? If it's a simple query, can you just
join the two queries together in a single SQL statement?

If not, you may have to loop through the recordset, concatenating all of the
IDNums into a single string and using the IN operator on that string. Let's
hope it doesn't have to come to that!

 

[Georgios Liakopoulos]

What are the details of recordset1? If it's a simple query, can you just
join the two queries together in a single SQL statement?

If not, you may have to loop through the recordset, concatenating all of the
IDNums into a single string and using the IN operator on that string. Let's
hope it doesn't have to come to that!

Thanks again Douglas. Yes, you've got the point. The recordset1 is not
opened through a simple query. In fact, there is no query at all. Just
random IDNum values depending on the user input (could be e.g.
10,13,54,120,130-133,201 that would create a recordset1 with 8 rows:
10,13,54,120,130,131,132,133,201).
Your second suggestion seems logical but what about if the user input is
something like: 1-6000 (!)?
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?

 

[Douglas J. Steele]

Thanks again Douglas. Yes, you've got the point. The recordset1 is not
opened through a simple query. In fact, there is no query at all. Just
random IDNum values depending on the user input (could be e.g.
10,13,54,120,130-133,201 that would create a recordset1 with 8 rows:
10,13,54,120,130,131,132,133,201).
Your second suggestion seems logical but what about if the user input is
something like: 1-6000 (!)?
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?

It sounds as though you may have to build the string, rather than
recordset1.

In other words, rather than changing the input to 8 rows, create a string
strValues that holds "10, 13, 54, 120, 130, 131, 132, 133, 201" (it doesn't
matter whether or not you have spaces after the commas), then change your
code to

recordset2.Open "SELECT * FROM Table1 WHERE " & _
"Table1.IDNum IN (" & strValues & ")", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Another option would be to create a temporary table, rather than a
recordset, in step 1 and then join to that temporary table.