Access 2007 Save Report as PDF VBA error

[Rx_]

A button opens a report, the report hides and shows a user form. The user form allows several pull-down, and options to be selected, then hides while the report generates. The report is in Print Preview mode.

Goal: The Report needs to allow the user to Save As a PDF.
One more problem: It runs on Citrix, so each user must Save As and pick the shared drive for thier region.

Trying to run the DoCmd.OutputTo ... code as the last line in the Report_Open event - gets an error.
After the report is finished - the code from the debugger window does create an Access 2007 Report to PDF file on my C:\ drive.

Two part Question:
Where would be the best place to run the DoCmd.outputTo...

Is there a "save as..." option that brings up a dialogue box for the user to pick a path?

? err. Number 2585
? err.Description This action can't be carried out while processing a form or report event.

DoCmd.OutputTo acOutputReport, "rptProjectSheet", acFormatPDF, strFileNameAndPath
Works from debugger window after report is completed and previewing
DoCmd.OutputTo acOutputReport, "Rx_ReportNewAPD", acFormatPDF, "C:\Access2007TestPDF1.pdf", True, , , acExportQualityPrint
Works from debugger window after report is completed and previewing - and it opens up the report in Adobe viewer.

Searching, I found this code, but don't know if it might be useful for Access 2007

' Note - found this, have not tried it, it may need a PDF printer or may not be Acc 2007
Option Compare Database
Option Explicit
Function PrintToPDF(SrcFile As String)
On Error GoTo PrintToPDF_Err
'Function can be called from any report with this: "PrintToPDF(Screen.ActiveForm.Name)"
'SrcFile = name of report the function was called from, as generated by Screen.ActiveForm.Name
'DestPath = Destination path for PDF file
Dim DestPath As String
'DestFile = Destination file name for PDF file
Dim DestFile As String
'ShowPdf = launch acrobat and display saved PDF file ' Question is this PDF Reader? or full Acrobat?
Dim ShowPdf As Boolean
'Saves the file to the desktop of the current user
DestPath = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\"
'Formats the file name like this: "YYYY-MM-DD-ReportNameHere.pdf"
DestFile = Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "-" & SrcFile
ShowPdf = False
DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestPath & DestFile & ".pdf", ShowPdf, "", 0, acExportQualityPrint
PrintToPDF_Exit:
    Exit Function
PrintToPDF_Err:
    MsgBox Error$
    Resume PrintToPDF_Exit
End Function

 

[boblarson]

You can use the Windows API (or the Filesystem objects) for getting a path. I like the API because there is no reference needed to be added.

As for PDF's, are you doing any merging of PDF's at all? We're struggling right now using ACG Soft's PDF PRO PLUS library for Access 2003 and while it works on local machines, it does not work on Citrix for us. I'm trying to get it to do so, but we're thinking that maybe we should move to Access 2007 (we will have to soon anyway) and then use the Output to for the files and then use the Acrobat SDK code to do the merge, page numbering, etc.

 

[Rx_]

What a speedy response.
I am not merging, but appreciate knowing that. Who knows what next week will bring? Your previous post on this subject really helped me out.

Moved the
DoCmd.OutputTo acOutputReport, "Rx_ReportNewAPD", acFormatPDF, "C:\Standard_Report 6-8-2010 11AM 23M 14S.pdf", True, , , acExportQualityPrint
to my Report GotFocus event

Beautiful, it saves a nice PDF file, and uses Adobe Reader 9 to open it automatically. Citrix shoudl do this too.
The beauty is (see attached file with some data blocked out) that Adobe Reader will allow the user to Save As - an pick a file path.
Since each regional user knows the path, they can rename it and save it as they see fit.

My goal is to give it a generic name with a Time Stamp so if the user saves it on Citrix, it won't conflict with an exising name:
i.e. Standard_Report 6-8-2010 11AM 23M 14S

Maybe I will print preview the report on Access long enough for the code to save a copy and open up Adobe Reader - and with a timer, just close the Access Report.

Bob, thanks for posting all the Previous things about saving to PDF on this site. It helped me kind of get things together.

One final thing worth noting:
Use the setfocus to bring the report screen to the forefront if other reports pop back up in front of the report.

 

[boblarson]

If you have a standard location (or even a chosen location) you should be able to modify this:

acFormatPDF, "C:\Standard_Report 6-8-2010 11AM 23M 14S.pdf",

to this:

acFormatPDF, "C:\Standard_Report " & Format(Now, "mm-dd-yyyy hhnnss") & .pdf",

or something like that.

 

[Rx_]

One last problem:
The report's GotFocus runs the
DoCmd.OutputTo acOutputReport, "Rx_ReportNewAPD", acFormatPDF, "C:\StandardReport" + strTimeStamp, True, , , acExportQualityPrint
and opens up Adobe viewer with the report (perfectlly).

Now, the report (in preview mode) just needs to be closed, and that takes users back to the Report Switchboard form.

Running this next (in the GotFocus)
Created an endless loop
Application.Echo False
DoCmd.Close acReport, "Rx_ReportNewAPD", acSaveNo
Application.Echo True

Any suggestion on where to put this code?

 

[boblarson]

Once you have done the OutputTo, you do not have control over the Acrobat object. You can't control it because you did not instantiate it as an object. And you can't do that because otherwise you can't use the OutputTo code.

 

[Rx_]

sorry I did not communicate it properly. Trying to close Access report, not Adobe.

I don't want to close the Adobe (and can't because as you say, it is in its own instance).

I want to close the open Access preview Report.
Using the code shown above to close the Access report (once Adobe started) created an endless loop.

 

[boblarson]

Well, first I would take out the Application.Echo part and see what happens.

 

[boblarson]

DoCmd.Close acReport, "YourReportName"

They already have that, just with the additional acSaveNo which is actually good. So, it shouldn't be that which is causing a problem. However, trying to close that report in the Got Focus, perhaps is.

 

[Rx_]

Very Minor change - missed a quote before the .pdf
acFormatPDF, "C:\Standard_Report " & Format(Now, "mm-dd-yyyy hhnnss") & ".pdf"

For people reading this:
My suggestion is to make a front-end dabase backup before hand.
It sometimes froze Access on a second try. My guess is dualing focus giving my resources had both processor cores maxed out.

Putting the event in the wrong place actually corrupted the Access
database. Granted - mine has a switchboard "report Form"
That opens a report -
the report itself halts to open a User Form to allow the user choose many options - those options construct SQL code that updates the Report.

In the process of trying to put it elsewhere, the exception reports, request to recover the database and just some strange behaviour was the result. Part of it may be how long it takes a report to generate while an event is on hold. But, all this went away when the Report_Deactivate event was used.

Problem:
The Report is on top. When clicking on the MainMenu and bringing it to the front, this triggers the code shown below.
Question: Is there a way to make the preview modal, or... a way to have the report preview close if it is clicked?

Private Sub Report_Deactivate()
Dim MyFiLeName      As String
    If Not NoData Then  ' checks for no data event to prevent a mis fire
        DoEvents
        MyFiLeName = "C:\Standard_Report " & Format(Now, "mm-dd-yyyy hhnnss") & ".pdf"
        DoEvents
        MsgBox "Please Save Your Adobe File to a network file location", vbOKOnly, "Save Now or loose it"
        DoEvents
        DoCmd.OutputTo acOutputReport, "Rx_ReportNewAPD", acFormatPDF, MyFiLeName, True ' , , , acExportQualityPrint
        ' The Now function just time stamps the file so all files are unique in name on citrix server for a user to save
        DoEvents
    End If
End Sub

 

[sailinxtc]

sorry if this is a highjack but was working on a similar situation with Pdf's and reports
Basically am trying to utilize the Save As dialog and fill in the name with [Log_ID].pdf One way I have it works but brings in the report name and will allow the user to change the name manually but would like to set the name automatic based on Log ID.

Private Sub Report_Load()
'Call PDF Creation
Dim pdfFile As String
Dim strPathAndFile As String
pdfFile = (Screen.ActiveForm.Name)
'This causes the Save As dialog with the report name as the file name and allows me to manually change it
strPathAndFile = ""
' This saves the Log_Id.pdf to My Documents but doesn't bring up the Save as dialog just saves the file
'strPathAndFile = (Log_ID & ".pdf")
DoCmd.OutputTo acOutputReport, (pdfFile), acFormatPDF, strPathAndFile, False
End Sub