How to take the result of a query to do new queries on this basis?

[Timo]

Hello everybody,

I have implemented a query in Access (by the select command), which carried out a number of lines respectively names depending on a criterion. What’s more I have integrated this query into a VBA code.

Now I would like to perform more queries on the basis of these values and I would like to select these names(lines in the table) by more and more new criteria.

For an initial idea I have used OpenRecordset and the MsgBox View to display the names outside the current table:




Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset("tmp")
MsgBox rst.Fields("name").Value
rst.MoveNext


Is there any way to take over these names into another query as a criterion in the select command?


Thank you and best regards,

Timo

[June7]

Looping code could build criteria string from the recordset object then apply that criteria to a form Filter property. Otherwise, have to use QueryDefs to modify query.

Perhaps this will be helpful http://www.allenbrowne.com/ser-62.html

[Timo]

Thanks a lot for your answer. Unfortunately I think a filter wouldn't be an appropriate solution for the wished selection.

Do you think it’s possible to do all the queries separately and then compare the “name column” to get matches? So for example I would get one table with all the people living in Berlin and I would compare this to a table with all people, who are 40. (automated by VBA)


I’m not sure, if this will be a reasonable solution, but I think there must be a possibility to create queries in Access that build on one another.

Best regards,
Timo

[June7]

Yes, queries can be used in other queries, just like a table. Can even nest queries.

However, I don't really understand your requirement and aversion to filtering form.

[Timo]

I would like to do something like this ("Help me decide"):

http://www.deere.com/wps/portal/dcom/productselector?categorySetName=Commercial+Mowing+ Equipment&categorySelected=true&siteName=en_US

The possible results should be reduced by every query resp. requirement. I did such a selection for step one, but I couldn’t find a way to build on these results for the second query.

[June7]

I won't even begin to guess how a web page is coded to deal with progressive searches.

The Allen Browne example is easiest way to build multi-parameter search criteria with VBA.

The intrinsic Access filter tool from the ribbon can do progressive filtering on table, query, form.

[Timo]

Okay, thanks a lot! I'm going to try it..