Looping with VBA

[Guest]

I'm a novice with VBA.

I need to design a "PRINT ALL" button. Right now, users can select an
office from a combo box on a form. A macro is set up and functioning that
gathers information and prints a report for that office. I need to use VBA
to loop through all the possible offices one by one and print the reports.

I would normally set this up so that a user could leave the control blank
and the format of the report would be such that all the reports would print
in order, but this particular report has 10 subreports and getting it to work
that way has prooved elusive. It would be preferable just to set up code to
loop through each of the reports.

Thanks for any help you can provide.

 

[Guest]

This code will print all reports in your database:

Dim rpt As AccessObject
For Each rpt In CurrentProject.AllReports
DoCmd.OpenReport rpt.Name, acViewNormal
Next

If you want to specify a subset of reports to print, you could store the
report names in a table, loop through the table records, and print from
there. Of course, this might be more tricky if any reports get criteria from
user input or from a form.

Barry

 

[Guest]

Thanks for the response.

That's not quite what I'm trying to do. The form control (Combo box) is
used as a criteria in a query that feeds a report. I'd like to loop through
each possibility for that control and print that same report. The combo box
is sourced by a table.

 

[Kc-Mass]

Could you not just do something like:

Dim db as Database
Dim rs as Recordset
Set Db = currentdb
Set rs as Db.openrecordset("ComboSourceTable", DbOpenSnapshot)
do while not rs.eof
DoYourReportGeneratorCommand with ComboSourceTable!CriteriaField
rs.movenext
loop