Trying to Export Current Record Report to PDF From Form Button

[wwconslt]

Hello Everyone,



Access 2019

I have a button on a form. When clicked, I would like the current record to open in a report I created and export to PDF format.

When I click the button, I get "The output to was canceled." error message.

Here is my code for the button:

==============

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click
If Me.Dirty = True Then
DoCmd.RunCommand acCmdSaveRecord
End If

DoCmd.OpenReport "rptRecord", acViewReport, , "[MASTER_ID] = [Forms]![frmMain]![MASTER_ID]"
DoCmd.OutputTo acOutputReport, "rptRecord", acFormatPDF, "C:\Users\Current Record.pdf", False
DoCmd.Close acReport, "rptRecord"




Exit_cmdPrint_Click:
Exit Sub


Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

=============

Thank you for any and all help!

[Welshgasman]

Shouldn't that be

"[MASTER_ID] = " &  [Forms]![frmMain]![MASTER_ID]

[Micron]

If that doesn't work, then master id is probably not a number.

[wwconslt]

Thank you for your help. MASTER_ID is an autonumber in the table record source.

When I use this code to preview the current record in the report it works fine.

DoCmd.OpenReport "rpt_Record", acPreview, "", "[MASTER_ID]=[Forms]![frmMain]![MASTER_ID]"
DoCmd.Maximize

But the end user would like the report to be exported straight to a pdf file without having to preview it first.

[Micron]

Well, IMO it should not work at all when written like that. I would suggest you try suggestion in post 2 and see if it makes a difference when not actually opening the report.

[Welshgasman]

Can you actually write to that folder?

[Gicu]

Maybe try to open the report hidden in print preview instead of report mode and save it in the same folder as the front-end (as you know you have permissions there):

DoCmd.OpenReport "rptRecord", acViewPreview, , "[MASTER_ID] = " & [Forms]![frmMain]![MASTER_ID], acHidden
DoCmd.OutputTo acOutputReport, "rptRecord", acFormatPDF, CurrentProject.Path & "\Current_Record.pdf", False '"C:\Users\Current Record.pdf", False
DoCmd.Close acReport, "rptRecord"

Cheers,

[June7]

Embedding a full path reference to control within quote marks does work. Access is able to evaluate the path. It was a surprise to me as well when I first discovered that.

The acCmdSaveRecord should work but another version is:

If Me.Dirty Then Me.Dirty = False

Review http://allenbrowne.com/bug-01.html

As already pointed out, folder permissions may be issue, otherwise, if you want to provide db for analysis, follow instructions at bottom of my post.

[wwconslt]

I can write to the folder--as I have code to export the whole data set to the same folder. I'm also able to open and preview the report with the record selected on the form. It just won't export to pdf.

[June7]

Well, cannot replicate issue with my db. So maybe provide yours as suggested?

Have you tried to a different folder? from a different computer?

[wwconslt]

Thanks everyone for your help. I was able to find some code that works:



Private Sub cmdPrint_Click()


Dim reportName As String
Dim criteria As String
Dim MSG As String
Dim FolderPath As String, fileName As String

DoCmd.Save
DoCmd.RefreshRecord




reportName = "rpt_Record"
FolderPath = "C:\Users"
fileName = FolderPath & Me.[MASTER_ID] & ".pdf"
criteria = "[MASTER_ID] = " & Me.[MASTER_ID]

'Exports report as PDF to Folder Path with Code Number as the file name

DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
DoCmd.OutputTo acReport, "rpt_Record", acFormatPDF, fileName, False
DoCmd.Close acReport, reportName, acSaveNo



MsgBox "Your file has been downloaded to C:\Users."





Exit_cmdPrint_Click:
Exit Sub



End Sub

[Welshgasman]

You have no trailing \ for the folder?, so you are writing to the root of C: ?

[June7]

So the only real significant difference is a dynamic instead of static filename. Destination folder is still same.

I had no problem with static filename. Output just replaced file.

Still cannot explain why original code failed.


Note that FolderPath = "C:\Users" line in your posted code is missing ending \ character. Enclose code in CODE tags and that would not be dropped. Please use CODE tags, especially with longer code snips, to retain indentation and readability and not lose \ character.

Unless you really did not have a \ character in code and actually are saving to C: root in which case "Users" becomes part of the file name.

[Gicu]

Sometimes I found (but this was with much earlier versions of Access) that using the actual value of the acFormatPDF (which is "PDF Format (*.pdf)") solved some unexpected problems\errors. In the OP's case I suspect it was the folder as I agree, the code posted saves the file in the root of C:\. I always tend to test exports like that in the same folder as the front-end as I know I have read\write permissions there.

Cheers,