Solved Filter and export multiple reports by same field with loop ability (1 Viewer)

[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
...

 

[Pat Hartman]

This is sort of what you need. This particular example is using selected items in a listbox to provide the loop. You need a recordset based on a query of the Locations table. The code is slightly different but the concept of the loop is what you need to understand. This code suffixes the report with a date range. It also looks up the high level path in a table. You would then concatenate the location part:
FileDir = FileDir & rs!Location & "\". The final tricky part is that because the OutputTo does NOT have an argument that you can use for a Where clause like the OpenReport method does, you have to have a different way of providing the location to the report. The cod has two commented out lines. The first Opens the report. I modified it so you could see how you would provide the location. and the second closes the report. Opening dozens of reports is extremely slow and may ultimately cause memory errors so the better method is to modify the RecordSource of the report to get the "location". In the example, as the code reads the loop of the listbox, it copies the current item to a form field (the field is hidden because no one needs to see it) and the report's RecordSource refers to that:
Select ... From ... Where LocationID = Forms!myform!txtWorkingRecord3 (I used my field so you can relate to my code. Make it whatever you want)

Private Sub cmdCoverLetters_Click()

Dim iT As Integer
Dim sPD As Variant
Dim strSPD As String
Dim FileDir As String
Dim sD As String
Dim eD As String
Dim rNow As String
Dim tDate As String
Dim stDocName As String
Dim FileName As String
Dim XFile As String
Dim RecCount As Integer

On Error GoTo Err_Proc
    iT = Me.lstCoverLetters.ItemsSelected.Count
    Me.txtTest3 = iT
    FileDir = DLookup("OutputPath", "tblFilePaths", "RecID = 1")
    If Right(FileDir, 1) = "\" Then
    Else
        FileDir = FileDir & "\"
    End If
    sD = Form_frmMain.txtStartDate
    eD = Form_frmMain.txtEndDate
    rNow = Format(Date, "yyyymmdd")
    tDate = rNow & "_" & Format(sD, "yyyymmdd") & "_" & Format(eD, "yyyymmdd") & "_"

    If iT > 0 Then
        For Each sPD In Me.lstCoverLetters.ItemsSelected
            strSPD = Me.lstCoverLetters.ItemData(sPD)
            Me.txtWorkingRecord3 = strSPD
            RecCount = RecCount + 1
            Me.txtCounter3 = RecCount
            stDocName = "rptCoverLetter"
            FileName = "rptCoverLetter_" & strSPD
            XFile = FileDir & tDate & FileName & " .pdf"
               ' DoCmd.OpenReport "rptCoverLetter", acViewPreview
                DoCmd.OutputTo acOutputReport, "rptCoverLetter", acFormatPDF, XFile, False
                'DoCmd.Close acReport, "rptCoverLetter", acSaveNo
        Next sPD
        Set sPD = Nothing
    End If

Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Proc
            Resume
    End Select
End Sub

Sorry about the code using a listbox instead of a recordset. I hope you can make the translation.

 

[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.