Loop Statement

[scott_ross_3]

I have a report that I need to run multiple times, and each time I need it to
run with the criteria created from a Select Query. The query will list a
group of names, and I need a separate report (to export to an RTF file) for
each record in the select query...preferably with the select query output
(a.k.a. the report criteria) as the file name.

I believe this requires a loop statement, but I'm not versed in the SQL
language. Can someone present me with some code that can make this happen?

 

[John Vinson]

I have a report that I need to run multiple times, and each time I need it to
run with the criteria created from a Select Query. The query will list a
group of names, and I need a separate report (to export to an RTF file) for
each record in the select query...preferably with the select query output
(a.k.a. the report criteria) as the file name.

I believe this requires a loop statement, but I'm not versed in the SQL
language. Can someone present me with some code that can make this happen?

You cannot loop in SQL - you'll need to use VBA. Without knowing
anything about the structure of your database I can't be sure, but I'd
guess that you need to open a Recordset based on the select query and
loop through it launching the report:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb ' get the current database
' Use it to open an uneditable fast snapshot recordset
Set rs = db.OpenRecordset("MySelectQuery", dbOpenSnapshot)
Do Until rs.EOF ' loop through all records in rs
' Open the Report using a criterion from the recordset
DoCmd.OpenReport "YourReportName", _
WhereCondition := "[fieldname] = " & rs!fieldname
' you'll need some other parameters for OpenReport
' depending on just what you want
rs.MoveNext ' go to the next record in rs
Loop ' it'll exit when you've reached the last record


John W. Vinson[MVP]