Export report to multiple PDFs (1 Viewer)

Jupie23 · Jan 4, 2018

I have found several threads on this topic, and have found some code to get started with in this thread:
http://access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto&page=2

But after trying several tweaks, I just can't quite get it to work. I am fairly new to Access and can handle vba for doing things on forms but recordsets and this type of code is a little over my head, so please if you have any suggestions, I will probably need it spelled out.

Here is what I'm trying to do: I have a small database to track errors found on accounts. I would like a pdf report to be saved for each processor [ProcessorName] on a drive, which would be done once a week. In another database, I have it set up to select the Processor in a combobox and export the report to the drive. This works fine, but that requires each processor to be selected individually. I would like it to save each processor's report in one click, if possible.

Here is what I have:
Query: qryUCCErrors - shows errors found by Processor
Form: frmReports - has txtStartDate and txtEndDate. The user enters the date range they would like to filter the report by, and the query references those controls as criteria for [QCDate].
Report: rptUCCErrors - Grouped on ProcessorName

Here is what I have for code:
It is currently stuck on the DoCmd.OutputTo line with a runtime error 2501: The OutputTo action was canceled. Also, I am totally guessing on the top section and the parameters. I don't know anything about this and am just using what I found in other threads.

Code:

Private Sub Command6_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryUCCErrors")
qdf.Parameters(0).Value = Forms!frmReports!txtStartDate
qdf.Parameters(1).Value = Forms!frmReports!txtEndDate
Set rst = qdf.OpenRecordset
Do While Not rst.EOF
    strRptFilter = "[ProcessorName] = " & Chr(34) & rst.Fields("ProcessorName") & Chr(34)
    
    DoCmd.OutputTo acOutputReport, "rptUCCErrors", acFormatPDF, "I:\Direct and Indirect Admin\Collateral QC Reports\" & "\" & rst![ProcessorName] & Format(Date, "mmddyyyy") & ".pdf"
    DoEvents
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub

Is anything standing out as incorrect? Don't mind the beginning of the drive, it is a network drive with the long path, but I took it out as it has the company name. Any help would be greatly appreciated!

Ranman256 · Jan 4, 2018

You have too many slashes in the filename: (This website keeps erasing my slash marks)
...Collateral QC Reports"SLASH & "SLASH" & rst![ProcessorName]

should be:
...Collateral QC Reports" SLASH & rst![ProcessorName]...

Jupie23 · Jan 4, 2018

Thank you for your response. I changed it as you suggested to:

Code:

Collateral QC Reports"\& rst![ProcessorName] & Format(Date, "mmddyyyy") & ".pdf"

and I get a Compile error: Syntax error.

Also, should the processor name part be as above or should it be rst.Field("ProcessorName")? In my search, I found it both ways and have tried both but not sure which to stick with.

Mark_ · Jan 4, 2018

Code:

"I:\Direct and Indirect Admin\Collateral QC Reports\" [COLOR="Red"]& "\"[/COLOR] & rst![ProcessorName] & Format(Date, "mmddyyyy") & ".pdf"

Should be

Code:

"I:\Direct and Indirect Admin\Collateral QC Reports\" & rst![ProcessorName] & Format(Date, "mmddyyyy") & ".pdf"

Jupie23 · Jan 4, 2018

Thank you, I have updated it as you said, but I am still getting the same runtime error and stuck on the same line of code.

Pat Hartman · Jan 4, 2018

It's always best to post your current code. There may be a typo that you are not seeing.

Mark_ · Jan 4, 2018

To take a step back and see if there is another error that is not obvious, I would add, after your last variable is declared

Code:

Dim asFilePath as String

and add

Code:

asFilePath = "I:\Direct and Indirect Admin\Collateral QC Reports\" & rst![ProcessorName] & Format(Date, "mmddyyyy") & ".pdf"
MsgBox "Saving file to " & asFilePath

just before you try to output the report. Make sure you KNOW where the file will be saved.

Jupie23 · Jan 5, 2018

My own stupid mistake! After changing the drive to my desktop as a test and it worked, I found when using the long network path that I was missing a folder. :banghead: For anyone else looking for it, this is the code that works to export each person's report to a folder:

Code:

Private Sub cmdExport_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryUCCErrors")
qdf.Parameters(0).Value = Forms!frmReports!txtStartDate
qdf.Parameters(1).Value = Forms!frmReports!txtEndDate
Set rst = qdf.OpenRecordset
Do While Not rst.EOF
    strRptFilter = "[ProcessorName] = " & Chr(34) & rst![ProcessorName] & Chr(34)
       
    DoCmd.OutputTo acOutputReport, "rptUCCErrors", acFormatPDF, "I:\Direct and Indirect Admin\QC Reports\" & rst![ProcessorName] & " - " & Format(Date, "mm.dd.yyyy") & ".pdf"
    DoEvents
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub

Thank you so much for your help!

Export report to multiple PDFs (1 Viewer)

Jupie23

Registered User.

Ranman256

Well-known member

Jupie23

Registered User.

Mark_

Longboard on the internet

Jupie23

Registered User.

Pat Hartman

Super Moderator

Mark_

Longboard on the internet

Jupie23

Registered User.

Similar threads

Users who are viewing this thread