Looping Access Reports Using VBA

[Keithman2]

How can I loop a report in access using VBA and create individual pdf docs? I have a table with 3 columns: Region, EmpId, and Flag. My current report groups by region and lists all the EmpID's. I need for it to loop and create a seperated pdf doc for each Region rather than one big pdf files containing all the region reports. Please Help!

 

[lameid]

Create a query that shows the regions. Use a recordset to loop through that and run the report based on the current region (filter / parameter query) in the recordset.

 

[Keithman2]

I am afraid my skills are some what limited when it comes to VBA. How do you create a recordset to loop through the regions?

 

[lameid]

The below code will print a report for each region...

[Green]'Requires Reference to DAO Library...
'Tools Menu, References "Microsoft DAO"... Should be checked
[/Green]
Dim Db as DAO.Database
Dim RS as DAO.Recordset

Set Db = Currentdb
Set RS = Db.openrecordset "Select Distinct Region From Table"

While NOT(RS.EOF)
    Docmd.openReport "Report Name", acViewNormal,,"Region = """ & RS!Region & """"
    RS.movenext
Wend

Depending on your PDF solution you may need to modify.