I have a situation where I need to take a report that is currently being printed on paper and convert each "Group By" to a separate report that can be emailed to a specific individual - and there are more than 800 of them. And this is done on a monthly basis. I expected this to be fairly straightforward - i.e. open a recordset in DAO that lists each of the entities, apply a WHERE criteria to the OutputTo statement and give each section a unique file name. Oops - OutputTo doesn't have a WHERE clause! Okay, I'll modify the query behind the report to restrict it to a specific section as I loop through each of the 800 and then it should work. Wrong again. It appears to work - it sits for 10 to 15 seconds, and then throws a 2501 Error with the message that the OutputTo command has been cancelled. The statement that I am executing is:
Code: Select all
DoCmd.OutputTo acOutputReport, strRPTName, acFormatPDF, NewFile, False
However, if I run the export of the report from the right-click/export command it works like a champ. A web search turns up a number of people experiencing the same problem, but none of them appears to have come up with a solution that will work going forward.
One alternative is to use the Stephen Lebans code to convert snapshot reports to PDF. However snapshots are not supported in 2010, and we expect to migrate to that version within the next year. The other alternative is to print the report out to PDF using Adobe Acrobat, as the client does have it on one of their workstations. However they do not have the Pro version, which as I understand it is the only one that will let you specify the file name in VBA.
Has anyone figured out a workaround for this issue? If so and you share it I will owe you big-time!