neilsolaris
Member
- Local time
- Today, 03:55
- Joined
- Apr 19, 2020
- Messages
- 114
Hi,
I have a report, which is linked to data in a query. The query is a list of invoices, and the first field is the unique invoice number.
If I filter the query by invoice number, and save it, when I open the report, it shows just that one invoice, so I am able to save it as a PDF manually. Is anyone able to help me with VBA code in order to loop through all the invoices, and save them as separate PDF files please?
I have copied and adapted some code I found online. It doesn't work yet, but then I wasn't expecting it to to be honest! The name of the invoice number field in the query is Invoice_Number. The query is called Q_Invoices and the report is called R_Invoices_PDF.
Thanks for your help.
I have a report, which is linked to data in a query. The query is a list of invoices, and the first field is the unique invoice number.
If I filter the query by invoice number, and save it, when I open the report, it shows just that one invoice, so I am able to save it as a PDF manually. Is anyone able to help me with VBA code in order to loop through all the invoices, and save them as separate PDF files please?
I have copied and adapted some code I found online. It doesn't work yet, but then I wasn't expecting it to to be honest! The name of the invoice number field in the query is Invoice_Number. The query is called Q_Invoices and the report is called R_Invoices_PDF.
Thanks for your help.
Code:
</>Private Sub cmd_GenPDFs_Click()
Dim rs As DAO.Recordset
Dim sFolder As String
Dim sFile As String
On Error GoTo Error_Handler
sFolder = "D:\Documents\Orchestra\Invoices\Invoice files\"
Set rs = CurrentDb.OpenRecordset("SELECT Invoice_Number FROM Q_Invoices", dbOpenSnapshot)
With rs
.MoveFirst
Do While Not .EOF
DoCmd.OpenReport "R_Invoices_PDF", acViewPreview, , "[Invoice_Number]=" & ![Invoice_Number], acHidden
sFile = Nz(![Invoice_Number], "") & ".pdf"
sFile = sFolder & sFile
DoCmd.OutputTo acOutputReport, "R_Invoices_PDF", acFormatPDF, sFile
'If you wanted to create an e-mail and include an individual report, you would do so now
DoCmd.Close acReport, "R_Invoices_PDF"
.MoveNext
Loop
End With
Application.FollowHyperlink sFolder 'Optional / Open the folder housing the files
Error_Handler_Exit:
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
Error_Handler:
If Err.Number <> 2501 Then 'Let's ignore user cancellation of this action!
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: cmd_GenPDFs_Click" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Sub</>
Last edited: