Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

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


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

Open in new window

Test_DB2.accdb
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Massimo Scola

ASKER

Hi now it works.

Thanks