Printing/Saving Multiple Reports to PDF (1 Viewer)

Oreynolds

Member
Local time
Today, 13:54
Joined
Apr 11, 2020
Messages
157
Hi,

I have the following sub routine which loops through a number of records and outputs and saves a PDF of the report in its respective folder. I am trying to make the process more efficient by it not opening the report each time and rather have it do it in the background. The trouble is when the report doesn't open the report filter doesn't kick in. Is there a better way of doing this?

Thanks

Code:
Private Sub cmdPDFSiteInfoRecord_Click()

Dim Folderpath As String
Dim RS As DAO.Recordset
Dim db As DAO.Database
Dim reportFilter As String
Dim currentFolderPath As String
    
    Set db = CurrentDb
    Set RS = db.OpenRecordset("SiteInfoNewQry")
    
    If Not RS.EOF Then
        RS.MoveFirst
        Do Until RS.EOF
            
            currentFolderPath = RS!Folderpath
                
            If FolderExists(currentFolderPath) Then
                      
            reportFilter = "SiteInfoID = " & RS("SiteInfoID") ' Assuming "ID" is the unique identifier field in the query
                
            DoCmd.OpenReport "rptPrintSiteInfoNew", acViewPreview, , reportFilter
            DoCmd.OutputTo acOutputReport, "rptPrintSiteInfoNew", acFormatPDF, currentFolderPath & "\rptSiteInfo.pdf", False
            DoCmd.Close acReport, "rptPrintSiteInfoNew"
            
            Else
            'MsgBox ("Folder path does not exist.")
            End If
                  
            RS.MoveNext
        Loop
    End If
    
    RS.Close
    
    Set RS = Nothing
    Set db = Nothing

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
21,288
You could try modifying the report to use a query instead of a table and then modify the SQL of the query in code to change the WHERE clause before exporting it to PDF.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 28, 2001
Messages
26,838
I am trying to make the process more efficient by it not opening the report each time and rather have it do it in the background. The trouble is when the report doesn't open the report filter doesn't kick in.

Putting the report in the background is tricky because is another instance of ACCESS also in the background? If not, how will you open an ACCESS-based report for the PDF conversion? But from the syntax you showed, it looks like a standard ACCESS report, which will probably need to have ACCESS doing something rather specific behind the scenes.

Stated more simply, your request is equal to saying I want to not use ACCESS to do an ACCESS-specific thing.

As you state the question "Is there a better way to do this?", I think the answer is a qualified NO.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:54
Joined
Jul 9, 2003
Messages
16,187
Instead of filtering the report, could you restrict the record set "the SQL" supplied to the report in the first place?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:54
Joined
Jul 9, 2003
Messages
16,187
See multiple reports with different criteria in my blog here:-

 

sonic8

AWF VIP
Local time
Today, 14:54
Joined
Oct 27, 2015
Messages
998
I am trying to make the process more efficient by it not opening the report each time and rather have it do it in the background. The trouble is when the report doesn't open the report filter doesn't kick in.
You must open the report supplying the criteria to filter to the intended records.
However, you can open the report hidden in the background. Here is an example for exporting multiple filtered PDFs.
 

Users who are viewing this thread

Top Bottom