Printing a Report to a PDF file with varying name and location

[Eutychus]

I am on a Windows XP machine using Access 2002-2003 writing an Access program for a Vista machine using Access 2003. I need to click a button on a form that will run a report which is set up to print using the "Adobe PDF" virtual printer. Then I need to set the file name and the file path to something I control each time I run the report, and close the dialog boxes when done. All this should happen automatically or transparent to the user. I don't want to use the Access report Snapshot view, but want to create a PDF file of the report. If I have to use the "Print" and "Save As" dialog forms, I want to be able to control using VBA the values on the "Print" and "Save As" dialog forms to give the report the name and to specify the path where the file will be saved.
I was able to "sort of" get some of what I wanted done with the following code in the OnClick event of the report button, but it worked sometimes and sometimes stopped on the Print dialog form waiting for the user to respond. SendKeys seems inconsistent. This code probably needs to be replaced with something better. Here it is . . .

Dim stDocName as String, MyFileName as string
stDocName = "MyFutureReport"
MyFileName = "MyReport.PDF"
DoCmd.OpenReport stDocName, acViewPreview
SendKeys "{TAB}", Wait
SendKeys "{TAB}", Wait
SendKeys "{ENTER}"
SendKeys MyFileName
SendKeys "{ENTER}"
DoCmd.RunCommand (acCmdPrint)
DoCmd.Close
Even when this code works, it does not control where the report is saved (the path) but I want to do that. It puts the report in the last folder a report was saved to. The report itself named "MyFutureReport" (above code) was set to print in page setup to a specific printer that was the "Adobe PDF" printer.
I've searched and searched and can't find out how to do this. Any help and specific code examples of something like the above will be greatly appreciated!!!

 

[terre]

I had a similar problem, and solved it here

http://www.lebans.com/reporttopdf.htm

Check it out

Terre

 

[Eutychus]

Thanks, terre. I tried the download from the site you supplied, but when I tried to open the unzipped A2000SnapshotToPDFver751.MDB file, I kept getting this error message: "This file is located outside your intranet or on an untrusted site. Microsoft Access will not open the file due to potential security problems." I tried opening the file in various ways, but each time I received the same error. I then went to Microsoft's support site and obtained the article Q303650 and followed the directions under Users. Same problem occurred. How did you get the file to open? Any ideas? Thanks again!

 

[terre]

The file opens on my computer. Is your intranet security the issue?

Can I email it to you?

 

[Eutychus]

After more digging, I found the problem. Here is the solution for any who may read this: To resolve the issue, try locating the .mdb file in Windows Explorer, right-click the file and chose Properties. In the "General" tab,
click "Unblock". Evidently, there is a system policy in the Attachment Manager of Windows XP Svc Pack 2 that blocks the downloaded files from running locally. When I followed the above instructions, I was able to open the MDB file. Now, I just have to wade through all that code and try to understand what's going on . . . . Any quick comments that might help shorten the wading time? Thanks again, terre!

 

[Eutychus]

Just a quick note to say the solution found at the link Terre provided worked for me. Many thanks!