Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-19-2019, 11:11 PM   #1
ReneeO
Newly Registered User
 
Join Date: Jul 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
ReneeO is on a distinguished road
Unhappy Export multiple PDF reports with recordset loop VBA

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

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
Attached Images
File Type: png 3075.png (6.0 KB, 10 views)
File Type: png Dialog.png (3.4 KB, 8 views)


Last edited by isladogs; 07-19-2019 at 11:40 PM. Reason: Added code tags
ReneeO is offline   Reply With Quote
Old 07-19-2019, 11:32 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,227
Thanks: 513
Thanked 917 Times in 869 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Export multiple PDF reports with recordset loop VBA

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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-19-2019, 11:39 PM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Export multiple PDF reports with recordset loop VBA

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-20-2019, 01:05 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,978
Thanks: 417
Thanked 714 Times in 693 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Export multiple PDF reports with recordset loop VBA

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] = '" & rst![DistinctClientName] & "'"
The dialog is because Access cannot find a field by that name, which I suspect is an actual client name?

HTH

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Tags
export , loop , reports , where clause not working

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to export recordset data to multiple excel range in same sheet jay9324 Modules & VBA 7 02-24-2015 08:54 AM
Button to Export Multiple Reports to Single PDF DeMarcus Forms 6 02-20-2014 10:09 AM
Open Multiple reports using For Loop paramesium Reports 4 10-26-2013 01:17 AM
How to export multiple reports & attach to same email Neobeowulf Reports 0 04-17-2013 12:24 PM
How to export Reports to Multiple worksheets in excel Snappy Modules & VBA 0 09-20-2012 06:33 AM




All times are GMT -8. The time now is 07:24 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World