CODEWITHSUNDEEP
ms-accessvbams-access-2007
user2214177
user2214177

Reputation: 1

How do I export a MS Access report to PDF with a formatted ID value as the report name?

I am using Access 2007.

I have created a button that allows me to output my current invoice to a .pdf and I want it to be named by "invoice number plus client name" and I am close but not quite there yet. This is the code I currently have and it works fine:

Private Sub Create_Document_Click()

    DoCmd.OutputTo acOutputReport, "Invoices", acFormatPDF, [Invoice_ID] & "_" & [Client_ID] & ".pdf"

End Sub

This gives me a report called "1_1.pdf" what I would like it to be called is "1007735001_XYZCompany.pdf".

In my Invoices table I have formatted the Invoice_ID field which is an auto number field as "1007735"00 but outputing the report names it as 1 instead of the formatted number, I would first like to know if I can change this to show the formatted value. Is it possible?

I would then like to know how I can change the client name value from 1 to the client's name. My field names are Client_ID and Client_Name and the table is called Clients.

I have searched through several forums now and tried many solutions but all have given me errors, Any help would be greatly appreciated. If I need to give more info please let me know.

Upvotes: 0

Views: 5710

Answers (1)

Fionnuala
Fionnuala

Reputation: 91366

You appear to have committed a number of sins. You should not add formats to tables, as you have found, the format is just a format, it does not change the content of the field ( http://blogs.lessthandot.com/index.php/DesktopDev/MSTech/MSAccess/AccessVBAJetSQL/why-you-should-not-add ). In additon, you appear to have added a look-up to ClientID, another anti-feature ( http://access.mvps.org/access/lookupfields.htm ).

You will need to look up the client again and reformat the number.

 Client = DlookUp("ClientName","ClientTable","ClientID=" & [Client_ID])
 Invoice = Format([Invoice_ID], """1007735""000")
 FileName= Invoice & "_" & Client & ".pdf"

Upvotes: 1

Related Questions