Export multiple PDF reports with recordset loop VBA

[ReneeO]

I'm rather stuck on a report filter and recordset. Code below results in a dialog on 1st round, the name of the 1st client. No matter what I type in, the report saves but is empty 2nd round of loop returns rtError3075 when assessing DoCmd.OpenReport section. Any support most appreciated

Dim rst As DAO.Recordset
Dim FOLDERPATH, FOLDER, FILEMONTH, FILENAME As String
Dim FILEYEAR As Integer
Dim REPORTOBJECT As REPORT
Dim ReportClient As String

Set rst = CurrentDb.OpenRecordset("SELECT DistinctClientName FROM tmpFilteredClients", dbOpenSnapshot)
   
' make sure that we have data
If rst.RecordCount > 0 Then

    rst.MoveFirst
     
    Do While Not rst.EOF
  
    Const REPORT_NAME As String = "rptClientActionReport"
    
    ' open the report setting the where parameter
     DoCmd.OpenReport REPORT_NAME, acViewPreview, , _
     "[Reports]![rptClientActionReport]![Group of ClientName] = " & rst![DistinctClientName], _
     acWindowNormal
     
    Set REPORTOBJECT = Reports![rptClientActionReport]

    'Settings for saving report
     FOLDERPATH = DLookup("FolderPath", "tblLIBSaveTo")
     FOLDER = Forms!frmClientReports!cboMonth
     FILEMONTH = Forms!frmClientReports!cboMonth
     FILEYEAR = Forms!frmClientReports!!txtYearSuffix
     FILENAME = FOLDERPATH & "" & FOLDER & "" & "ClientReports_" & FILEMONTH & "20" & FILEYEAR & ".pdf"
    
    ' save the opened report
     DoCmd.OutputTo acOutputReport, REPORT_NAME, acFormatPDF, FILENAME

     DoCmd.OutputTo acOutputReport, "rptClientActionReport", acFormatPDF, FILENAME
                           
    ' close the report
      DoCmd.Close acReport, REPORT_NAME

    DoEvents
    rst.MoveNext
Loop
                
End If ' rst.RecordCount > 0

rst.Close
Set rst = Nothing

 

[Uncle Gizmo]

I couldn't make head nor tail of your code. Possibly because I'm viewing it on my mobile.

I direct you to my website where I have noted the steps I took in helping another Access World Forums user with a similar problem a while back.

https://www.niftyaccess.com/generate-multiple-reports/

I demonstrate the progression from the OP's original problem to a final working solution.

Basically there are two record set loops, one which generates the reports, and one which provides each report iteration with a unique record source.

There is also a link to Gina Whipps website where she demonstrates how to email each iteration of the report if so desired .

Sent from my Pixel 3a using Tapatalk

 

[isladogs]

The thread was moderated. Posting here to trigger email notifications.
I also added code tags to make the code easier to read.
For future posts, please use the code tag button (#) on the toolbar.

 

[Gasman]

I'd be walking through the code line by line in the debugger.

Your parameter needs to be surrounded by single quotes or triple double quotes if there is a chance of a single quote in the parameter?

"[Reports]![rptClientActionReport]![Group of ClientName] = [COLOR="Red"]'[/COLOR]" & rst![DistinctClientName] & [COLOR="red"]"'"[/COLOR]

The dialog is because Access cannot find a field by that name, which I suspect is an actual client name?

HTH