Printing/Saving Multiple Reports to PDF

[Oreynolds]

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

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]

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]

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]

Instead of filtering the report, could you restrict the record set "the SQL" supplied to the report in the first place?

 

[Uncle Gizmo]

See multiple reports with different criteria in my blog here:-

Generate Multiple Reports – Nifty Access

www.niftyaccess.com

 

[sonic8]

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.