Help with For Loop in MS Access

[sesling]

I am using MS Access 2010.
I have a query that will return 3 values (ZZZ070113,ZZZ070213,ZZZ090713) here is the query (SELECT DISTINCT RUN_ID FROM CUSTOMER where customerID = 1567). These variables will change from time to time. Some times there will be 2 or 4 ID's returned

I want to take the results and loop through a series of commands to extract records from another table and write the results to a new table.
So based on some research I have done I figure I need to use a For Each loop statement

First is how do I assign the values from the query to a variable?
Then how do I pass these results into a loop so I can see the results?



Ex. The code would take ZZZ070113 and query table Customer_Details to retrieve ProcessDate and ProcessCount. These value would be written to a new table called CustomerDetails...Then the code would take ZZZ070213 and do the same etc..

I am not having much success in my searches to find out how to make this work. Any assistance I could get would be greatly appreciated

[June7]

Why do you need to write to another table? A query that joins tables should be able to return the results.

Your options:

1. open a recordset object in VBA and loop through recordset to read each value

2. open a form based on the query and have VBA procedure cycle through the form records and read each value

[sesling]

The reason I need to write the results to another table is because of our internal audits. I need to be able to show the date/time the query was ran and by who. I was able to create a recordset object in VBA and loop through each ID. The similar threads that got attached to this thread provided some good pointers as well. Thx for your response.

[June7]

Still don't see why that requires duplicating those records to another table. Could instead have a table that simply saves record of the query or report name, date opened, and by whom.