Export multiple PDF reports with recordset loop VBA (1 Viewer)

ReneeO

New member
Joined
Jul 20, 2019
Messages
3
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 :eek:

Code:
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
 

Attachments

Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Joined
Jul 9, 2003
Messages
10,832
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

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,327
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

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
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?

Code:
"[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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom