Massimo Scola
asked on
Batch Export Reports (with multiple parameters) As PDF
I had this question after viewing Report with several filters - Issue with query?.
The following procedure prints all invoices for a particular month.
Today, I was asked by my boss to not only print my reports on paper but also to save/export them as PDF.
The DoCmd.OutputTo acOutputReport doesn't seem to work in this case as the output report does not allow me to pass parameters.
Is there a way to somehow print these reports as PDF? Should there be a procedure when the report loads?
The form 'PrintInvoices' has a filepicker which selects the path for the output and the variable strReportName concatenates the customer's name with today's date.
Thanks
Massimo
The following procedure prints all invoices for a particular month.
Today, I was asked by my boss to not only print my reports on paper but also to save/export them as PDF.
The DoCmd.OutputTo acOutputReport doesn't seem to work in this case as the output report does not allow me to pass parameters.
Is there a way to somehow print these reports as PDF? Should there be a procedure when the report loads?
The form 'PrintInvoices' has a filepicker which selects the path for the output and the variable strReportName concatenates the customer's name with today's date.
Thanks
Massimo
Sub PrintAllInvoices(OrderMonth As Integer, OrderYear As Integer, Optional CustomerID As Integer)
Dim DB As Database
Set DB = CurrentDb
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
'array variable - contains all customer IDs
Dim arrResults() As Variant
Dim I As Integer
Dim intCount As Integer
'get customers for this month and year - using SQL Distinct CustomerID
Set qdf = CurrentDb.QueryDefs("qyrOrdersByMonth_UniqueCustomers")
qdf.Parameters("@ordermonth").Value = OrderMonth
qdf.Parameters("@orderyear").Value = OrderYear
Set rst = qdf.OpenRecordset
intCount = rst.RecordCount
Dim strReportName As String
Dim strCustomerName As String
With rst
If Not .EOF Then
Do Until .EOF
CustomerID = .Fields("CustomerID")
strCustomerName = DLookup("CustomerName", "Customers", "CustomerID = " & CustomerID)
strReportName = strCustomerName & " " & Format(Now(), "DD.MM.YYYY", vbMonday, vbUseSystem) & ".pdf"
'Debug.Print "Report name: " & strReportName
Dim strFIlter
strFIlter = "[discountmonth]= " & TempVars("InvoiceMonth") & " and [discountyear]= " & TempVars("InvoiceYear") & "and [ordermonth]=" & TempVars("InvoiceMonth") & " and [orderyear]= " & TempVars("InvoiceYear") & " and [customerID]=" & CustomerID
DoCmd.OpenReport "Invoice", acViewPreview, , strFIlter
DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, TempVars("PDF Path").Value & strReportName & strReportName, False
DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, "Invoice"
.MoveNext
Loop
End If
.Close
End With
'close this connection
'rst.Close
Set rst = Nothing
DB.Close
Set DB = Nothing
End Sub
Test_DB2.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks