OutputTo PDF Error 2501 (Access 2007 SP2)

[Wendell]

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!

[JohnH]

I don't know if this will solve your problem, but an approach I have used in similar situations (docmd.sendobject does not support a where clause either) is to:

* Open the report in Preview mode, using a Where clause.
* Use docmd.SendObject
* close the report

In this situation, SendObject uses the currently open report, so it applies the Where clause.
I have just tried this with OutPutTo and it worked the same way.

[Wendell]

I'm not sure I'm following you - will SendObject allow you to export the file to PDF? We have some 800 of these where we need to save the report in a format readable by people who we have no control over what software they are using - could be Macs for example - and we need to send the report as an attachment to an email. I was planning to use Automation of Outlook to do the emailing - in some cases there may not be a file to attach.

[JohnH]

I was not suggesting that you use SendObject, just drawing similarities with the behaviour of SendObject

Base the report on a query that returns all 800 or so records.

Use the recordset as you mentioned, and loop through all 800 records.
For each record, open the report in Preview Mode using a Where clause. Then use Output To to create the pdf. The OutputTo will use the currently open report, so it will apply the Where clause. Close the report, and move on to the next record.

[JohnH]

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.

I have just been trying this out, and I am pleased to find that Stephen Leban's code still works for me with 2010. (using mdb file format, but I don't think that is relevant.)

So I tested this, and it also worked in 2010.

Code: Select all

  DoCmd.OutputTo acOutputReport, stDocName, "SnapshotFormat(*.snp)"

[Wendell]

Well, after 12 days in Italy, and a week to recover from jet lag and a cold, I finally got back to this and I was still getting the same error using the principal of opening the report in preview mode first and then trying to do the output to PDF. And as usual, it turned out to be an entirely different problem. I was using a UNC path to store the output file, and had a typo in the server name. The 2501 error was trying to tell me it couldn't find the place to store it! So now it works just fine, and I suspect my prior approach of changing the data source for the report would have worked too. I'll probably take that approach as watching the preview of some 800 reports would get pretty annoying. Thanks John for your suggestions and help.

[JohnH]

You could probably suppress the screen output with
docmd.echo false
.
.
.
docmd.echo true

if you took this route.

It is surprising how often that a seemingly intractable problem turns out to be something quite unrelated to where you are looking.