Filter report in VBA loop not working

[M.]

What I'd like to do is to generate a pdf of a filtered report rpt for every
record in the recordset rst, based on the filter defined in the field
Report_Filter of this recordset in the following way:

'Report rpt already opened earlier in code
'Set filter condition for current record
rpt.Filter = rst!Report_Filter
'Set filteron to activate filter
rpt.FilterOn = True
'prepare path and filename to save pdf of report to on harddisk
strAttach = "c:\TEMP\" & rst!ID & ".PDF"
'Generate pdf output of filtered report and save to harddisk
DoCmd.OutputTo acOutputReport, , acFormatPDF, strAttach

This procedure works fine when I'm debugging the code line by line, so I can
see that the filter is activated and the report refreshed accordingly, but
when I run the code in full speed execution mode, the filter condition
appears to be ignored and every record gets the same (unfiltered) pdf with a
different filename.

I also noted that when I open the report every time with a different where
condition(Docmd.openreport.....), this works fine, but is very slow for my
purpose. I would like to stick with opening the report once and only updating
the filter condition to save execution time for the large recordsets that
have to be processed.

What am I missing here between debugging mode and normal execution mode that
results in this difference?

Thanks in advance,

M.

 

[M.]

BTW I'm using Access/Office 2007,

M.

 

[Ken Snell [MVP]]

OutputTo exports the report's underlying Recordsource query and ignores any
filtering that may have been set for the report. You will need to create
filtered queries and export each query independently.

 

[M.]

Dear Ken,

I've just discovered two solutions:

1)filtered query + requery of report before output to statement
2)DoCmd.OpenReport+filter criterion without closing the report inbetween
different output to actions

Both solutions are now equal in process time (100 reports in 6-7 secs), so
I'm happy! Thanks for your suggestion, although I don't fully understand the
recordscource issue. Does the recordsource also change when you use the
docmd.openreport command + filter statement?

Best regards,

M.

 

[Ken Snell [MVP]]

The "docmd.openreport command + filter statement" limits the records in the
report's RecordSource before the report opens, so that the report contains
*only* the filtered records. This is done by ACCESS using the report's
Filter property (similar to what you were doing), but ACCESS causes the
report's records to be filtered first, then the report is opened (so the
report contains only the records that fit the filter). When you change the
Filter property after the report has opened with all records, you're
filtering the display of the records, not the actual records contained in
the report.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/