Create a seperate PDF Report for each company

[Lloyd]

Using Access 2007, I output a report to a singe PDF file that consists of
invoices grouped by company. i would like to create a seperate PDF for each
company. Some companies invoice data consists of multiple pages. I am
attempting to do this using the OutPutTo command. I need help with filtering
the data for each report. Each Report will be title companyName.PDF. My code
is:

Dim rs As DAO.Recordset
Dim db As Database
Dim CompanyName As String
Set db = CurrentDb
Set rs = db.OpenRecordset("rpt-hgrpinv")

Do While Not rs.EOF
CompanyName = rs!CoShortName
strRepName = "MyReportName"
strPath = "C:\folder\"
strCustomFileName = CompanyName & " Invoice Report"
DoCmd.OutputTo acOutputReport, strRepName, acFormatPDF, strPath &
strCustomFileName & ".pdf", False, "", 0, acExportQualityPrint

rs.MoveNext
Loop
End

Lloyd

 

[Dale Fye]

Lloyd,

There is no parameter in the OutputTo method that allows you to pass any
kind of a filter to the report as it is being output. So, you could try a
couple of workarounds

1. Modify the RecordSource of your report so that it filters the company
based on a field on your form, or a global variable. In your loop, you could
modify the value of the control on your form, then, because the report uses
that control to filter the report, the OutputTo method should work.

2. Another method that I think should work would be to open the report
prior to your loop. Then, change the Filter property of the report so that
it is only displaying the current company :

Docmd.openreport "yourReport", acViewPreview
Do While ...

....
reports(0).Filter = "[CoShortName] = """ & rs!CoShortName & """"
Docmd.OutputTo ...

rs.movenext
Loop