Expert guide

Export Microsoft Access Reports to PDF with VBA

One record, one customer, or a whole batch, with sensible file names every time

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: DoCmd.OutputTo acOutputReport, "rptName", acFormatPDF, filePath exports a report to PDF. To export just one record, open the report hidden with a WhereCondition first, then OutputTo, then close it. For one PDF per customer, loop a recordset doing the same, building safe file names and folders. Our Access reports and forms service builds these exports into invoice, statement and certificate workflows.

Monthly statements, invoices, certificates of analysis, job sheets: the most common Access automation request is "make a PDF of this report for each customer and save it". Access has had built-in PDF export since 2010, but the details of filtering are where most attempts go wrong.

The Basic Export

DoCmd.OutputTo acOutputReport, "rptMonthlySales", acFormatPDF, _
               "C:\Reports\Monthly Sales.pdf", False

The final False stops the PDF opening automatically. Use True when a user clicks "Preview PDF" and wants to see it.

Export One Record

Public Sub ExportInvoice(ByVal invoiceID As Long, ByVal outPath As String)
    DoCmd.OpenReport "rptInvoice", acViewPreview, , "InvoiceID=" & invoiceID, acHidden
    DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, outPath, False
    DoCmd.Close acReport, "rptInvoice", acSaveNo
End Sub

Because the report is already open with the filter, OutputTo exports that filtered instance. If you call OutputTo without opening it first, Access opens a new, unfiltered copy, which is why people end up with every invoice in one PDF.

One PDF per Customer

Public Sub ExportStatements(ByVal periodEnd As Date)
    Dim rs As DAO.Recordset, folder As String, fileName As String, n As Long

    folder = "\\fileserver\Statements\" & Format(periodEnd, "yyyy-mm") & "\"
    EnsureFolder folder

    Set rs = CurrentDb.OpenRecordset( _
        "SELECT DISTINCT c.CustomerID, c.AccountNo, c.CompanyName " & _
        "FROM tblCustomers AS c INNER JOIN tblInvoices AS i ON c.CustomerID = i.CustomerID " & _
        "WHERE i.Balance > 0", dbOpenSnapshot)

    Do While Not rs.EOF
        fileName = folder & SafeFileName(rs!AccountNo & " - " & rs!CompanyName) & ".pdf"
        DoCmd.OpenReport "rptStatement", acViewPreview, , "CustomerID=" & rs!CustomerID, acHidden
        DoCmd.OutputTo acOutputReport, "rptStatement", acFormatPDF, fileName, False
        DoCmd.Close acReport, "rptStatement", acSaveNo
        n = n + 1
        SysCmd acSysCmdSetStatus, "Exported " & n
        rs.MoveNext
    Loop
    rs.Close
    SysCmd acSysCmdClearStatus
    MsgBox n & " statements saved to " & folder, vbInformation
End Sub

Public Function SafeFileName(ByVal s As String) As String
    Dim bad As Variant
    For Each bad In Array("\", "/", ":", "*", "?", """", "<", ">", "|")
        s = Replace(s, bad, "-")
    Next bad
    SafeFileName = Left$(Trim$(s), 120)
End Function

Public Sub EnsureFolder(ByVal path As String)
    Dim parts() As String, i As Long, cur As String, first As Long
    If Left$(path, 2) = "\\" Then                       ' UNC: \\server\share\...
        parts = Split(Mid$(path, 3), "\")
        cur = "\\" & parts(0) & "\" & parts(1) & "\"     ' server and share must exist
        first = 2
    Else                                                ' drive: C:\...
        parts = Split(path, "\")
        cur = parts(0) & "\"
        first = 1
    End If
    For i = first To UBound(parts)
        If Len(parts(i)) > 0 Then
            cur = cur & parts(i) & "\"
            If Dir(cur, vbDirectory) = "" Then MkDir cur
        End If
    Next i
End Sub

Company names often contain characters Windows does not allow in file names, such as slash, colon and question mark; SafeFileName replaces them. EnsureFolder creates the month folder (and any missing parents) on a UNC path.

Design the Report for PDF

  • Set the page size and margins explicitly in Page Setup; PDF output follows them.

  • Use Keep Together on group headers so a customer's lines do not split awkwardly.

  • Put the record identifier and a page number in the footer (Page [Page] of [Pages]).

  • Avoid controls that only make sense on screen, such as buttons or hidden fields.

  • Use a font available on every PC that will export, to avoid substitution.

Common Problems

ProblemCauseFix
PDF contains all recordsReport not open with the filter at export timeOpen hidden with WhereCondition first
Blank PDFFilter matched nothingCheck the criteria; skip records with no data
Error 2501 (action cancelled)Report's NoData event cancelled itHandle 2501 and skip, or remove Cancel from NoData
Error 2302 can't save output dataFolder missing, no permission, or file open in a PDF viewerCreate folder, check permissions, close the file
Slow batch exportsReport record source with domain functionsBase it on a joined query; see the DLookup guide

Slow reports often use domain functions per row; see faster alternatives to DLookup.

Next Steps: Email and Summaries

Exported PDFs are usually the attachment of an email; the complete pattern is in sending email from Access with Outlook VBA. For management packs, an approved AI-written summary at the top of the report is covered in AI summaries in Access reports. Excel users needing the same for invoices should read bulk-creating PDF invoices from Excel.

FAQ

Access report to PDF FAQ

Filtering, batches and errors

Use DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "C:\Path\File.pdf", False. The last argument controls whether the PDF opens automatically. Access 2010 and later can create PDFs without any add-in.

Open the report hidden in preview with a WhereCondition, such as DoCmd.OpenReport "rptStatement", acViewPreview, , "CustomerID=42", acHidden, then call DoCmd.OutputTo for the same report name, and close it. OutputTo uses the already-open, filtered instance.

Loop through a recordset of customer IDs, and for each one open the report filtered to that ID, export it to a file name that includes the customer name or number, and close it. Clean the file name of characters Windows does not allow.

A blank PDF usually means the filter matched nothing or the report was closed before export. All records means the report was not open with the filter when OutputTo ran, so Access opened a fresh, unfiltered copy. Open the report hidden with the WhereCondition first.

Access exports one report per file. To combine, either design a single report with subreports, or merge PDFs afterwards with a PDF tool or library. For packs such as a monthly board report, a master report with subreports is usually the cleaner solution.