Filtering trouble with Access Report split into mutliple PDF's

[tucsonhanny]

I have code for outputting an Access report into multiple PDF's, but I am having trouble with the filtering.

The code below does create multiple pdf files but it doesn't filter the results. All pages show up each report. When I switch the OpenReport method to acViewPreview to test, it correctly displays only the pages it should in preview.

Can anyone tell what I am doing wrong?

The two report methods:

DoCmd.OpenRepor t strRptNm, acViewNormal, , "ProviderNa me = '" & ProviderNm & "' "


DoCmd.OutputTo acOutputReport, strRptNm, acFormatPDF, strFileNm


The entire code:

Dim ProviderNm As String
Dim PathNm As String
Dim RptNm As String
Dim strFileNm As String
Dim Sql As String
Dim db As Database
Dim rs As Recordset


strPathNm = "C:\RVU\"
strRptNm = "rptProvider"



Sql = "SELECT DISTINCT ProviderName FROM qryProvider"
Set db = CurrentDb
Set rs = db.OpenRecordset(Sql)

Do Until rs.EOF

ProviderNm = rs!ProviderName
strFileNm = strPathNm & ProviderNm & "\Productivity\" & "2011-08" & ".pdf"


DoCmd.OpenReport strRptNm, acViewNormal, , "ProviderName = '" & ProviderNm & "' "


DoCmd.OutputTo acOutputReport, strRptNm, acFormatPDF, strFileNm


DoCmd.Close acReport, strRptNm


rs.MoveNext

Loop


rs.Close
Set rs = Nothing
Set db = Nothing

[patjones]

The reason for this is that the OutputTo line does not have anything to do with the OpenReport line. They are independent of each other. Thus the filtering has no effect on the OutputTo execution. As far as I can tell, there is no way to filter when using OutputTo.

[NeoPa]

Try :

Call DoCmd.PrintOut(...)

on line #28 instead of OutputTo().

[tucsonhanny]

Neo, I got a type mismatch error 13 on that line when I replaced the OutputTo()

Call DoCmd.PrintOut( acOutputReport, "rptProvide r", acFormatPDF, strFileNm)

[tucsonhanny]

Ok, the method takes different parameters. But it doesn't allow you to specify an output type and a file name to save the pdf's.

Thanks, anyway.

[tucsonhanny]

I discovered the issue.

DoCmd.OpenRepor t strRptNm, acViewNormal, - Filter DOESN'T apply

DoCmd.OpenRepor t strRptNm, acViewPreview - Filter DOES apply

[NeoPa]

Now I'm confused.

Are you saying that running the OutputTo command after the report has been opened is effected by the open report? The Help page for OutputTo implies quite strongly this is not how it works (although it stops short of stating so explicitly).

[patjones]

tucsonhanny

I discovered the issue.

DoCmd.OpenRepor t strRptNm, acViewNormal, - Filter DOESN'T apply

DoCmd.OpenRepor t strRptNm, acViewPreview - Filter DOES apply

We really need to separate the OpenReport part of this from the OutputTo part. The distinction is that OpenReport will open the report for viewing within the Access window, while OutputTo exports the report to an external file (be it PDF, Excel, text, etc.). You are having problem with OutputTo.

Can you clarify for us whether the result of using OutputTo is affected by how you use OpenReport? It would be new to me, but one learns something unexpected all the time around here.

[tucsonhanny]

The OutputTo will take the report results of an open report of the same report name.

I have read posts that mention that since the OutputTo doesn't have any filter parameters then the only way to filter the report results (without changing the underlying report query at each pass) is the open the report first, then output it.

What I didn't realize was the when the report is set as acViewNormal the filter doesn't apply apparently because the results are sent to the printer before the output method is run. acViewPreview opens the report and allows the OutputTo to take it's filter settings.

avViewNormal doesn't make sense to use anyway when you're saving the report and not printing it since it bypasses the preview mode and goes straight to printing. I had switched it from preview while trouble-shooting other issues and forgot to move it back to preview.

[NeoPa]

What confused me in the Help about it was that it was apparently for any named object, rather than the currently open/selected object, but it makes sense after all if you omit the ObjectName parameter :

Access VBA Help:
ObjectName
Optional Variant. A string expression that's the valid name of an object of the type selected by the ObjectType argument. If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank. If you run Visual Basic code containing the OutputTo method in a library database, Microsoft Access looks for the object with this name, first in the library database, then in the current database.

Thanks for posting what you found :-)