Others

How to Split Microsoft Access Reports into Separate PDF Invoices

Amos Gikunda Oct 9, 2026 870 views

Question details

The user needs to export a Microsoft Access report into multiple, separate PDF files (one for each invoice) instead of generating a single, continuous PDF document containing all records.

How to Split Microsoft Access Reports into Separate PDF Invoices

Product: Microsoft Access
Device & OS: not provided
Scenario: Exporting multiple invoice records to distinct PDF files programmatically using a VBA loop in Access.
Observed behavior: The current VBA loop exports the entire, unbroken report repeatedly under different filenames because the report's underlying record source is not filtered for each loop iteration.

Before you start

Ensure you have a basic understanding of Visual Basic for Applications (VBA) and have created a backup copy of your Access database before modifying any queries or VBA modules.

Solution 1Recommended

Filter the Report Record Source Using a Hidden Form Control

The most reliable method is to restrict the report's underlying query to a single invoice number using a WHERE clause that references a form control during each loop iteration.

To prevent Access from exporting all records into one file, the report's RecordSource must be filtered to one specific invoice at a time before the export command is executed.

Create a Reference Control

Add a hidden form control (for example, a text box named 'txtInvoiceList' on 'frmInvoicesDlg') to hold the current invoice number during the loop.

Modify the Record Source Query

Update your report's underlying query to include a WHERE clause that points to this control, such as: WHERE [InvoiceNumber] = [Forms]![frmInvoicesDlg]![txtInvoiceList].

Update the VBA Loop

In your VBA code, set the hidden form control to the current invoice number before executing the output. For example: Forms!frmInvoicesDlg!txtInvoiceList = rs!InvoiceNumber.

Export and Close

Use DoCmd.OpenReport in hidden mode, followed by DoCmd.OutputTo to export the filtered report to a specific PDF path, and then call DoCmd.Close before the loop moves to the next record.

Filter the Report Record Source Using a Hidden Form Control

Dynamic Filenames: Ensure your VBA code dynamically generates a unique filename (e.g., appending the invoice number to the string) for each PDF output to prevent files from overwriting each other.

Solution 2

Pass Filter Parameters Directly in the OpenReport Command

An alternative approach is passing a WHERE condition string directly when opening the report via VBA, avoiding the need to modify the underlying query structure.

Free Microsoft Office alternative

Discover a Lightweight Alternative to Microsoft Office

While Microsoft Access handles complex database reporting, WPS Office provides a free, lightweight, and robust suite for all your document, spreadsheet, and PDF management needs. It features a highly familiar interface and built-in PDF tools for seamlessly handling your exported reports.

1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install WPS Office: Run the downloaded file and follow the straightforward on-screen instructions to set up the software.
3. Manage Your Documents: Launch WPS Office to open, edit, or split your exported PDF reports and Office documents effortlessly.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Built-in robust PDF toolkit for easy editing, splitting, and merging of PDF files.Lightweight installation that runs smoothly on both modern and older devices.Familiar user interface requiring zero learning curve for Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA loop export the entire database into every PDF?

The DoCmd.OutputTo action exports the report exactly as it is defined in its RecordSource. If the report's query isn't dynamically filtered to a single record during the loop iteration, Access will continuously export the full, unbroken report.

Can I split the invoices after exporting them as one large PDF?

Yes, if the report has already been exported as a single long PDF, you can use dedicated PDF editing software, such as the WPS Office PDF toolkit, to manually extract or split the pages into separate documents.

How do I hide the Access report while exporting it to PDF?

You can use the 'acHidden' constant as the WindowMode argument in the DoCmd.OpenReport method. This processes the report in the background without visibly rendering it on your screen, speeding up the PDF export process.

Office Quick Solutions