Well Hello Bill Mosca!
It's great to hear from you. It's been quite a while. I hope you are well. I agree AD was a genius! I'll check it out.
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 Bill Mosca via groups.io
Sent: Wednesday, June 25, 2025 2:02 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Saving Invoices to individual PDFs
Hi Doyce I've been away from Access for a few years now, but I remember vaguely that I approached multiple invoices on one report with each invoice being a subreport. You might find something you can use at http: //www. rogersaccesslibrary. com/forum/tejpal-a-d_forum45. html
| This Message Is From an Untrusted Sender | | You have not previously corresponded with this sender. | | |
Hi Doyce
I've been away from Access for a few years now, but I remember vaguely that I approached multiple invoices on one report with each invoice being a subreport. You might find something you can use at http://www.rogersaccesslibrary.com/forum/tejpal-a-d_forum45.html
AD was a blooming genius when it came to finding solutions with anything that had to do with reports.
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
