Save a Report as a PDF with custom filename

[andymartin3186]

Hi Guys
I've tried to create a macro based on another users post from 2020. I want to click a button on my job form, which saves the single report matching the current displayed jobID and saves it as a PDF on the desktop. I would also like the filename formatted as Quotation M00000 where the zeros are the JobID (autonumber so if its 95 for example it is stored as 00095)
The issue is I can't even get it to save, it brings up the error as per the attachment.
I know I have to add a format() expression. Does this go as a seperate macro line?

If someone could help that would be great

 

[Gasman]

Enclose your template with the format function

 

[andymartin3186]

Apologies

Enclose your template with the format function

Could you elaborate what you mean with that? I'm reasonably new to Access

 

[andymartin3186]

Just as a side note if I leave the output file field blank, it saves correctly after asking me where to save.

 

[andymartin3186]

I've decided to use VBA to do this rather than macros


Dim Filename As String
Dim FilePath As String

Filename = "Quotation" & " " & Me.JobID
FilePath = "C:\Users\Andy\Desktop\" & Filename & ".pdf"

DoCmd.OutputTo acOutputReport, "Quotation", acFormatPDF, FilePath, , , , acExportQualityPrint

It works great the only think I'm unsure of is how to format the filename so its always 00000 rather than just the digits of the ID.
Is there a command for this?

 

[CJ_London]

Format(ID,”00000”)

 

[andymartin3186]

Got it!

Dim Filename As String
Dim FilePath As String

Filename = "Quotation" & " " & "M" & Format(Me.JobID, "00000")

FilePath = "C:\Users\Andy\Desktop\" & Filename & ".pdf"

DoCmd.OutputTo acOutputReport, "Quotation", acFormatPDF, FilePath, , , , acExportQualityPrint

 

[arnelgp]

you can also use:

Filename = "Quotation" & " " & Format(Me.JobID, "\M00000")

 

[RonPaii]

If any chance this will be done multiple times per JobID, add a loop to create revisions.

Dim Filename As String
Dim FilePath As String
dim i as long

do
    Filename = "Quotation" & " " & "M" & Format(Me.JobID, "00000") & _
                iif(i=0,vbnullstring,"R" & i)
    i = i + 1
    FilePath = "C:\Users\Andy\Desktop\" & Filename & ".pdf"
while dir(FilePath) <> vbnullstring)

DoCmd.OutputTo acOutputReport, "Quotation", acFormatPDF, FilePath, , , , acExportQualityPrint

 

[Gasman]

Got it!

Dim Filename As String
Dim FilePath As String

Filename = "Quotation" & " " & "M" & Format(Me.JobID, "00000")

FilePath = "C:\Users\Andy\Desktop\" & Filename & ".pdf"

DoCmd.OutputTo acOutputReport, "Quotation", acFormatPDF, FilePath, , , , acExportQualityPrint

Exactly like that in your macro.
I do not use macroes myself.

 

[andymartin3186]

Exactly like that in your macro.
I do not use macroes myself.

Many thanks for your help