Export Acess Report into Single One Page PDF (1 Viewer)

VBANEWBIE

Registered User.
Local time
Today, 08:42
Joined
Oct 17, 2016
Messages
17
Hello,
I have an Access DB with a report that contains 1000s records, looking to export into multiple PDFs while filtering on an unique field name and then using that same field in the naming convention. I am fairly new to the VBA world so an help would be greatly appreciated. thanks
 

sneuberg

AWF VIP
Local time
Today, 05:42
Joined
Oct 17, 2014
Messages
3,506
Could you tell us a little more about this unique field? Some examples of it would be helpful. How many PDF are to be created? Do you have a rule in mind like for example 100 records per PDF?
 

VBANEWBIE

Registered User.
Local time
Today, 08:42
Joined
Oct 17, 2016
Messages
17
the unique field is a combo of letters and numbers that actually makes up the "CustomerID", no two are alike.

as for the quantity of PDF, most will be 1 or 2 pages but the script should create a new PDF when the CustomerID (unique field) field changes.
 

sneuberg

AWF VIP
Local time
Today, 05:42
Joined
Oct 17, 2014
Messages
3,506
If you have 1000 records each with unique CustomerID and the script should create a new PDF when the CustomerID (unique field) field changes then you want 1000 PDFs. Is that correct?

That's not a problem I just want to make sure that's what you want. How do you want the file name? Just the customerID.PDF ?
 

sneuberg

AWF VIP
Local time
Today, 05:42
Joined
Oct 17, 2014
Messages
3,506
You can do this by opening a recordset of the CustomerIDs and looping through them, each time opening a report and outputting it to PDF. The report can be filtered by the CustomerID in the report open. The following code which has been tested in the attached database implements this.

Code:
Dim ReportName As String
Dim OutputFolder As String
Dim rs As DAO.Recordset

ReportName = "TestReport"       'Replace with report name
OutputFolder = "C:\Users\sneuberg\Desktop\TestFolder"   'Replace with folder name

Set rs = CurrentDb.OpenRecordset("SELECT CustomerID FROM Customers") 'Replace Customers with table name
Do While Not rs.EOF
    DoCmd.OpenReport ReportName, acViewReport, , "[CustomerId] = '" & rs!CustomerID & "'", acHidden
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, OutputFolder & "\" & rs!CustomerID & ".PDF"
    DoCmd.Close acReport, ReportName
    rs.MoveNext
Loop
rs.Close

To use this code you will need to create a report that has the output you want. The recordset of that reports needs to have as a minimum the CustomerID. You will need to modify this code to change the ReportName, the OutputFolder, and the name of the table to match your situation.

This code overwrites previous files without warning. If that's a problem let me know and let me know how you want to handle it.

This will probably take quite a bit of time to output 1000 PDFs. I don't know if the speed can be increased or not. Please let us know how it goes.
 

Attachments

  • OutputPDFs.accdb
    420 KB · Views: 304

VBANEWBIE

Registered User.
Local time
Today, 08:42
Joined
Oct 17, 2016
Messages
17
A Millions Thanks !!!, it worked exactly as you stated.
 

Users who are viewing this thread

Top Bottom