Re: [MSAccessProfessionals] Saving Invoices to individual PDFs

Paul,

 

You hit the nail on the head!  Thank you.  I swear I tried this but it must have been something similar and not quite right.  😊

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Paul Baldy via groups.io
Sent: Wednesday, June 25, 2025 2:27 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Saving Invoices to individual PDFs

 

You would need the bang to indicate it's a value from the recordset. Presuming your debug is returning the correct value, try the same syntax: DoCmd. OpenReport sReportName, acViewPreview, , "[Invce_31]='" & rs. Fields("INVCE_31") & "'"

ZjQcmQRYFpfptBannerStart

This Message Is From an Untrusted Sender You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd

You would need the bang to indicate it's a value from the recordset.  Presuming your debug is returning the correct value, try the same syntax:

 

DoCmd.OpenReport sReportName, acViewPreview, , "[Invce_31]='" & rs.Fields("INVCE_31") & "'"

 

Paul

------ Original Message ------

From: "Doyce Winberry via groups.io" <doyce.winberry=xpo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <MSAccessProfessionals@groups.io>

Sent: 6/25/2025 12:13:05 PM

Subject: Re: [MSAccessProfessionals] Saving Invoices to individual PDFs

 

Hi Paul,

 

I've added the delimiters like this:

DoCmd.OpenReport sReportName, acViewPreview, , "[Invce_31]='" & [INVCE_31] & "'", acHidden

 

I'm getting runtime error 13 Type Mismatch.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Paul Baldy via groups.io
Sent: Wednesday, June 25, 2025 2:08 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Saving Invoices to individual PDFs

 

You haven't mentioned the error you get. Since the field is text, you'd need the delimiters: https: //www. baldyweb. com/wherecondition. htm Paul ------ Original Message ------ From: "Doyce Winberry via groups. io" <doyce. winberry=xpo. com@ groups. io>

ZjQcmQRYFpfptBannerStart

This Message Is From an Untrusted Sender You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd

You haven't mentioned the error you get.  Since the field is text, you'd need the delimiters:

 

https://www.baldyweb.com/wherecondition.htm

 

Paul

 

------ Original Message ------

From: "Doyce Winberry via groups.io" <doyce.winberry=xpo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <msaccessprofessionals@groups.io>

Sent: 6/25/2025 11:12:10 AM

Subject: [MSAccessProfessionals] Saving Invoices to individual PDFs

 

Hello Friends,

 

I'm trying to save an invoice report as individual invoices, then I want to email them out. First the saving part.  I found a code sample that I am modifying but I'm hung up on filtering the report.  Its criteria is that it has N in the print flag.  If I just open the report, it runs fine.  In the VBA, I want to save a copy of each invoice as a separate PDF and name it with the invoice number.  The code is hanging when I try to filter the report with this line: DoCmd.OpenReport sReportName, acViewPreview, , "[Invce_31]=" & ![INVCE_31], acHidden.  Invce_31 is the invoice number, but it is a string. I've tried all kinds of qualifiers '" & Invce_31 & "'" but it always errors out.  I've also tried DoCmd.OpenReport "MaxInvoiceNoPrint", acViewPreview, , "[Invce_31]='" & 00197273 & "'" in the immediate window where 00197273 is a legitimate invoice and the report opens but there is no data. Below is the whole procedure:

 

Public Sub ExportInvoices()

    Dim rs                    As DAO.Recordset

    Dim sFolder               As String

    Dim sFile                 As String

    Const sReportName = "MaxInvoiceNoPrint"

 

    'On Error GoTo Error_Handler

 

    'The folder in which to save the PDFs

        sFolder = \\ltpfp7460.amer.corp.xpo.com\Accounting\Accts Receivable\invoice copies & "\"

        'Debug.Print sFolder

 

    'Define the Records that you will use to filtered the report with

    Set rs = CurrentDb.OpenRecordset("SELECT INVCE_31 FROM qryMaxInvoiceNoPrint;", dbOpenSnapshot)

    With rs

        If .RecordCount <> 0 Then 'Make sure we have record to generate PDF with

            .MoveFirst

            Do While Not .EOF

            Debug.Print rs.Fields("INVCE_31")

                'Build the PDF filename we are going to use to save the PDF with

                sFile = sFolder & Nz(![INVCE_31], "") & ".pdf"

                'Open the report filtered to the specific record or criteria we want in hidden mode

                DoCmd.OpenReport sReportName, acViewPreview, , "[Invce_31]=" & ![INVCE_31], acHidden

                'Print it out as a PDF

                DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile, , , , acExportQualityPrint

                'Close the report now that we're done with this criteria

                DoCmd.Close acReport, sReportName

                'If you wanted to create an e-mail and include an individual report, you would do so now

                .MoveNext

            Loop

        End If

    End With

 

    'Open the folder housing the PDF files (Optional)

    Application.FollowHyperlink sFolder

    MsgBox "Report pages exported as individual PDFs."

 

End Sub

 

Any help figuring this out is greatly appreciated. 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269