Run Time Error 2501 When Outputing Report, Office 2010

[TimTDP]

I have the following code to print to report to pdf

DoCmd.OpenReport strReportName, 2, , Wclause, acHidden
DoEvents
DoEvents
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, FolderPath & "\" & strReportCommonName & ".pdf", False, , , acExportQualityPrint
DoCmd.Close acReport, strReportName, acSaveNo

I first open the report in hidden mode so that I can filter the records on the report

Occassionally I get an error code 2501 - "Action was cancelled" on line DoCmd.OutputTo.........

I thought it might be because the report is still opening in line DoCmd.OpenReport....... hence the DoEvents, but this does not help!

The funny thing thing is that the error is not constant.
I am testing on one report with the same data and criteria.
6 times out of 10 no error will occur!

When the error occurs and I select debug, the problematic code "DoCmd.OutputTo acOutputReport...." is highlighted in yellow.
If I hit F5 the code will eventually run! I may have to hit F5 a couple of times, but the code will run and the file will be created.

How can I automate a simillar action to hitting F5?
I need to get the report to file!

Thanks

 

[vbaInet]

Tim, you would normally need to close the report before calling the OutputTo command. However, I don't see how you're filtering the report by simply opening it in hidden mode.

Have a look at this link and in it I explain how to filter the report before calling OutputTo:

http://www.access-programmers.co.uk/forums/showthread.php?t=204478

 

[TimTDP]

Opening the report with DoCmd.OpenReport ... filters the report. I hide it because the user does not need to see it.
Then when using the DoCmd.OutputTo acOutputReport... the pdf report is referenced to the open report. Don't ask me how, but it works! The pdf report is corectly filtered!

I have tried applying a filter to the report in the reports On Open event using:
Me.Filter = strRptFilter
Me.FilterOn = True
but the code never runs!

I have placed a breakpoint on the line of code "Me.Filter = strRptFilter" but as I wrote the code never runs!

The reports recordsource is a query built into the report. By clicking ... on the right of the recordsource property and then building the query

 

[vbaInet]

You need to completely forego your approach and follow what was explained. If you're trying to do both at the same time it will of course fail.

The code will not break because the OutputTo command disables that feature as it will interrupt with its function. You can set a Debug.Print strRptFilter and look at the Immediate Window to see what is printed. Or use a Msgbox.

 

[TimTDP]

Many thanks for the explanations.

As mentioned, the recordsouce of the report is a query built into the report.
How could I apply the filter using a querydef?
The query is fairly complicated so I don't want to create it in code!
Failing which I will consider one of the following:
* creating the report's recordsource as an actual query
* writing the data to a temp table

 

[vbaInet]

The approach presented has nothing to do with temp tables and doesn't require one, neither does it require the underlying record source of the report to be altered.

Let's see a stripped down version of your db (saved in an earlier version of course - 2007).

 

[TimTDP]

Found the problem!

I use a string called Wclause in which I build the filter criteria.
I declared it as Public Wclause as String
I repeated in the print module as Dim Wclause as String

Thanks so much for your efforts.

 

[vbaInet]

I repeated in the print module as Dim Wclause as String

A re-declaration of the variable won't be necessary as long as you have declared it as public in a Module.

Happy to help!