Export multiple reports automatically with VBA

In the attached database, I’m able to export a single report to a PDF file in a specified directory.  The data included in the report is dependent on what criteria is selected in my combo boxes.

When you select the zone ‘Atlantic’ in the combo box, you have a choice of two areas: ‘MD’ and ‘NJ’.  First select MD, then click the [Execute] and a report will be exported to the C drive.  

Instead of having to manually select each area individually to generate a report, I’m looking for a block of code that will generate a report automatically for each Area.  The report would be named after the Area, and it would be exported to a sub folder named after the Zone.

So a report with the Zone ‘Atlantic’ selected and the Area ‘MD’, the file would be exported to the following path: C:\Atlantic\MD.pdf

I really appreciate any feedback.  I realize this is a tough one.
Database1.accdb
KP_SoCalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This is really more of a project than a request for help.

You can loop through the Areas associated with a Zone like this:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblMaster WHERE Zone='" & Me.cboZone.Column(0) & "'")

Do Until rst.EOF
  DoCmd.OpenReport "repDaily", , , "Area='" & rst("Area") & "'", acHidden
  DoCmd.OutputTo acOutputReport, "repDaily", acFormatPDF, Me.txtPath
  DoCmd.Close acReport, "repDaily"
Loop

As far as "outputting to a subfolder" ... you could alter Me.txtPath to point to a subfolder. You can check to see if the folder exists:

If Dir(Me.txtPath & "\" & Me.txtZone) = "" Then
  '/ the folder does not exist - use MkDir to create it
Else
  '/ the folder does exist - do you need to empty it first?
End If

To create a new folder, if needed, use MkDir:

MkDir Me.txtPath & "\" & Me.txtZone

Then refer to that:

  DoCmd.OutputTo acOutputReport, "repDaily", acFormatPDF, Me.txtPath & "\" & Me.txtZone & "\" & rst("Area") & ".pdf"

Your user would have to be sure to select a Folder, and not enter a full document name/path.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KP_SoCalAuthor Commented:
I couldn't get this to work, especially the Loop, so I feel I need to break this up into multiple questions in separate posts.  I appreciate you taking the time in pointing me in the right direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.