Prev: Conditional formatting to be visible or invisible
Next: subquery without using EXISTS word (?)
From: Lloyd on 14 Nov 2009 16:21
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
From: Dale Fye on 16 Nov 2009 12:15
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

----
HTH
Dale



"Lloyd" wrote:

> 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
 | 
Pages: 1
Prev: Conditional formatting to be visible or invisible
Next: subquery without using EXISTS word (?)