Using parameters in code?

[Shane]

how do you pass the results from one recordset to be used
for parameters for another recordset

This is what I want to do.

I have a list box that shows parts on order.

When i click the part I want it to find all the purchase
orders that have that PO/Part relationship (One-many)
using a sql statement.
my result from that query would be example:
PO_IDS
426
538
567

I want to use those Id's as parameters for another sql
statement so I can view those purchase orders. Would this
be an array or a loop of some sort?

 

[Allen Browne]

Concatenate the values together, to use with the IN operator in the WHERE
clause. You need to end up with a SQL statement that looks like this:
SELECT * FROM MyTable WHERE PO_IDS IN (426, 538, 567);"

Example:

Dim rs As DAO.Recordset
Dim strWhere As String
Dim lngLen As Long

Set rs = dbEngine(0)(0).OpenRecordset("SELECT ...
Do While Not rs.EOF
strWhere= strWhere & rs!PO_IDS & ", "
rs.MoveNext
Loop
rs.Close

lngLen = Len(strWhere) - 2 'without trailing comma and space
If lngLen > 0 Then
strWhere = "[PO_IDS] IN (" & Left$(strWhere, lngLen) & ")"
End If

 

[Marshall Barton]

how do you pass the results from one recordset to be used
for parameters for another recordset

This is what I want to do.

I have a list box that shows parts on order.

When i click the part I want it to find all the purchase
orders that have that PO/Part relationship (One-many)
using a sql statement.
my result from that query would be example:
PO_IDS
426
538
567

I want to use those Id's as parameters for another sql
statement so I can view those purchase orders. Would this
be an array or a loop of some sort?

Not sure I followed that. You say recordset in one place,
but later you talk about a query.

I can't be sure, but it sounds like you might be able to use
a single query:

SELECT PO_ID, . . .
FROM potable INNER JOIN partslist
ON potable.PO_ID = partslist.PO_ID
WHERE partslist.partnnumber = FORMS!theform.thelistbox

If I've missed the boat, please post back with more details
about the tables and their fields.