I am working with a fairly large database of information. I need to use a distinct set of values in one saved query as the parameter in the 'where' clause for another query. What is the best way to iterate through the result set of distinct values? I am fairly new to Access and VBA.

3 Answers
3

I may not understand the question, because I'm interpreting it completely differently from the other answers.

It seems to me that the others have answered assuming that "What is the best way to iterate through the result set of distinct values" is the key part, where I'm concentrating on "I need to use a distinct set of values in one saved query as the parameter in the 'where' clause for another query".

There are two ways to approach this:

the standard Access way: save the other query, add it to the first query and join on the field that you're filtering on.

What does passthrough have to do with this? That applies only to a server database, and there's no mention of anything in the question other than Access itself.
–
David-W-FentonAug 13 '10 at 0:26

Well, as you mentioned in your method 1, if it's not a pt query, they should be able to use a join instead of VBA. If he wants to iterate through a result set, he needs to use VBA, but I've only done that to replace variables in pt queries. I really don't see enough information to fully understand what he's trying to do.
–
BethAug 13 '10 at 15:21

@Beth's answer is probably the best and if you wish to post some details of your tables and queries, I am sure a more detailed answer can be provided, however, if you need to iterate through the query results for some reason other than to obtain a second result set, you can use a recordset. In VBA:

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim sSQL As String
''You open an SQL string, a table, or a query as a recordset
Set rs = CurrentDB.OpenRecordset("QueryName")
Do While Not rs.EOF
sSQL= "SELECT Some, Fields FROM ATable WHERE ID = " & rs!NumericID
Set rs2 = CurrentDB.OpenRecordset(sSQL)
''Now what?
rs.MoveNext
Loop