Solved Filter and export multiple reports by same field with loop ability

[Bgwade]

Hello,

Any help would be greatly appreciated. I have a database with the following components.

Table1 - Applicant details, includes a text field for location.
Table2 - List of locations

Queries Source - Table1
Query1-With Human Resources
Query2 -Pending Interview
Query3-Offer Made

Report1 - Recruiter, based off of Query1
Report2 - Interviewer, based off Query2
Report3 - Supervisor, based off Query3

I am trying to put together a code that would filter reports 1,2, and 3 by the same location and export as pdfs to a folder. The folder's name would match the filtered location. I want to loop this action through all 56 locations. I have tried and failed so many different ways I struggle to communicate them to you. Thank you in advance for any ideas you may have. These reports would be run on a bi-weekly or monthly basis.

 

[theDBguy]

Hi. Welcome to AWF!

What you are asking to do doesn't seem to be impossible, but it would be hard to offer any specific advice without knowing details about your problem. Maybe you can post the code you tried, and we can suggest modifications to help make it work.

 

[plog]

Divide and conquer.

One of the main errors I see on this site is somebody trying to do 6 hard things all at once and they have no idea which specific hard thing is the cause of their problem. So break this down into parts and tackle each part seperately and then bring them all together. Further, don't do 56 things, do one thing then make it work for 56.

Turn every verb into a step:

I am trying to put together a code that would filter reports 1,2, and 3 by the same location and export as pdfs to a folder.

1. Build a function to use 1 location and 1 report and get it to open that report to that location.

2. Build a function to export a report as a .pdf. Don't even filter it, just export the thing.

3. Build a function to create 1 directory. Name it whatever you want, just see if you can get it to make a directory.

4. Build a function to loop through every location you have. Just have it Debug.Print each location.

Boom, that's the parts. Once they work seperately, stitch them together to get what you want.

 

[arnelgp]

sample demo of what you need to accomplish.
my table may not match your tables' structure.

 

[Uncle Gizmo]

There's an answer to a particular OP question on my website which covers some of what you want to do. See here:-

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

 

[Bgwade]

The closes I have gotten is the below code I found. It opened and exported a single report with all the different location names. However, the filtering portion did not work so it was the same location on all the exports.

Dim db as Database
Dim rst as Recordset
Dim strFile As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Location FROM Table1", dbOpenDynaset)

Do While Not rst.EOF
' OPEN FILTERED REPORT IN PRINT PREVIEW '
DoCmd.OpenReport "Report1", acViewPreview, , "Location = " & rst!Location

' OUTPUT REPORT INTO PDF '
strFile = "C:\Path\To\Output\Folder\Report1_" & rst!Location & ".pdf"
DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, strFile, False

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing

It would prompt for a parameter value at the beginning, and it would list a location name. I tried creating Table2, the list of location names, thinking that might have been the issue and changed the below with the same results.

Set rst = db.OpenRecordset("SELECT Location FROM Table2", dbOpenDynaset)

 

[theDBguy]

The closes I have gotten is the below code I found. It opened and exported a single report with all the different location names. However, the filtering portion did not work so it was the same location on all the exports.


It would prompt for a parameter value at the beginning, and it would list a location name. I tried creating Table2, the list of location names, thinking that might have been the issue and changed the below with the same results.

Hi. Try inserting this one line of code before rst.MoveNext: DoCmd.Close acReport, "Report1", so it will look like this:

...
' OUTPUT REPORT INTO PDF '
strFile = "C:\Path\To\Output\Folder\Report1_" & rst!Location & ".pdf"
DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, strFile, False

DoCmd.Close acReport, "Report1"

rst.MoveNext
Loop
...

 

[Bgwade]

Thank you for all the responses! I was able to set a looping filter and export action to one report so I am going to recreate with the other reports.

 

[theDBguy]

Thank you for all the responses! I was able to set a looping filter and export action to one report so I am going to recreate with the other reports.

Congratulations! Glad to hear you got it sorted out. Good luck with your project.