Looping through results of a query to run a report

[peter.nelson]

I have a pretty complex database that uses several queries that all lead to a final query with a parameter (order number). I have a report linked to that query so every time I open the report it asks me for an order number. Once I enter the order number the query is run and I print the report. I am curious if I can create a query that pulls out order numbers (possibly for a date range) and then have a macro or something loop through that query and print a report for each order number. So if the query returned 50 order numbers then the report with the parameter (called Final results) would be printed 50 times. Is there a way to do this?

[June7]

Yes, definitely. Open a recordset of the desired order numbers and loop through the recordset using the orderID as filter criteria for the report. I only use VBA, like:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.open "SELECT OrderID FROM Orders WHERE <some criteria here>;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
While Not rs.EOF
DoCmd.OpenReport "reportname", , , "OrderID=" & rsOrderID
rs.MoveNext
Wend