Export Reports to pdf

[Gerrit.B]

In my database I have created report (rptInvoice) based on query (qryInvoice) that includes all customers.

Is is possible to export the report to pdf, 1 (one) pdf for each customer (CustomerID), and save the reports as customerID.pdf.

Regards,

G

 

[Kreszch68]

Hi, assuming you are using Access 2007 or higher you can accomplish this as follows.
First you’ll need a control structure to enable looping through all Customer ID’s. Best option would be creating a form (let’s name this frmCreateInvoices) with a listbox on it and two command buttons (one to create all invoices, one to create a single invoice for a selected customer id).
As I don’t know anything about you’re database, I’ll stick to the ‘qryInvoice’ for the examples I’m giving you.
To use the examples, first create a user form named frmCreateInvoices (although the name of the form is not relevant in this case).
Then create the next controls on the form:

listbox named lstCustomerID
command button cmdAllInvoices
command button cmdOneInvoice

In the load event of your report place the next code:

Private Sub Report_Load()
Me.RecordSource = "Select * From qryInvoice " _
                & "Where CustomerID = '" & Me.OpenArgs & "'"
End Sub

In the load event of your form place the next lines of code

Private Sub Form_Load()

'Set the listbox properties and data
With Me.lstCustomerID
    .ColumnCount = 1
    .RowSource = "Select CustomerID from qryInvoice"
End With

End Sub

And for the command buttons use the next code

Private Sub cmdAllInvoices_Click()
'Create an invoice for each customer
'Here we do'n really need the listbox, as we can derive the Customers from the query
'but for the sake of the example we use the listbox anyway

Dim i As Integer
Dim lstBox As ListBox

Set lstBox = Me.lstCustomerID

For i = 0 To lstBox.ListCount - 1
HandlerCreateInvoice lstBox.ItemData(i)
Next i

End Sub

Private Sub cmdOneInvoice_Click()
'Although the command button is called OneInvoice, we can use it to create multiple invoices.
'Make sure to set the multiselect property to EXTENDED
'Create an invoice for each selected customer

Dim vItem As Variant
Dim lstBox As ListBox

Set lstBox = Me.lstCustomerID
'Test if user made a selection
If lstBox.ListIndex = -1 Then
    MsgBox "Select at least one customer"
End If

For Each vItem In lstBox.ItemsSelected
        HandlerCreateInvoice lstBox.ItemData(vItem)
Next vItem

End Sub

Now you have the first part set up, the next step is creating a new module with the code to actually create the PDF's.
The complete code is:

Option Compare Database
Option Explicit

Public Sub HandlerCreateInvoice(ByVal sCustomerID As String)

[B]Const sDefaultPath As String = "c:\temp\"
[/B]Const sReportName As String = "rptInvoice"

Dim sPDFName As String

sPDFName = sDefaultPath & sCustomerID & ".pdf"
CreatePDF sPDFName, sCustomerID, sReportName

End Sub

Public Sub CreatePDF(ByVal sPDFName As String, _
                     ByVal sCustomerID As String, _
                     ByVal sReportName As String)

Dim rpt As Report
'Open the report, giving sCustomerID as OpenArg
DoCmd.OpenReport sReportName, acViewReport, , , , sCustomerID

Set rpt = Reports(sReportName)

DoCmd.OutputTo acOutputReport, rpt.Name, "PDFFormat (*.pdf)", sPDFName, , , , acExportQualityPrint
DoCmd.Close acReport, rpt.Name

End Sub

I guess from here you'll know what to do. But feel free to ask any questions.

 

[Kreszch68]

Erratum: Of course the line for the report has to be in the OPEN event and NOT in the load event.

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "Select * From qryInvoice " _
                & "Where CustomerID = '" & Me.OpenArgs & "'"
End Sub

 

[Gerrit.B]

Thanks, this works perfect.

 

[Gerrit.B]

Private Sub Form_Load()
'Set the listbox properties and data
With Me.lstCustomerID
.ColumnCount = 1
.RowSource = " SELECT qryInvoice.CustomerID FROM qryInvoice GROUP BY qryInvoice.CustomerID; "
End With
End Sub

Changed BOLD code above to avoid creating reports multiple times for the same customer.

 

[Kreszch68]

Ok, you could also use, Select DISTINCT qryInvoice.CustomerID FROM qryInvoice.