How can I output a report in PDF format, where the name consists of values from fields?

Question

I want to add functionality to my Access 2007 report whereby a PDF copy of the report is created at the click of a button. I know that there is an OutputTo macro which can do this for me, but it does not allow me to include report field values as part of the PDF's filename, namely: [Client Organisations].Code + "-" + Clients.Code + "-" + Invoices_Code + "-" + Format([Invoice Date],"yyyy") + ".pdf".

While I have seen this MSDN thread and this SO question, I don't see the use of field values in any of the answers.

I reckon VBA code is the way to go, so I (unsuccessfully) tried the following:

Private Sub Create_PDF_Click()
DoCmd.OutputTo acOutputReport, , acFormatPDF, "" + [Client Organisations].Code  
+ "-" + Clients.Code + "-" + Invoices_Code + "-" + Format([Invoice Date],"yyyy")
+ ".pdf", True
End Sub

Run-time error '2465':

Microsoft Office Access can't find the field '|' referred to in your expression

Any ideas out there?

Answer 1

Its easier to Dim a string and put your entire expression there and then debug it to see if it contains the valid report name.

Like this:

Dim strReportName as String

strReportName = [Client Organisations].Code   
+ "-" + Clients.Code + "-" + Invoices_Code + "-" + Format([Invoice Date],"yyyy") 
+ ".pdf"

//then try to print strReportName before you use DoCmd.OutputTo.

Answer 2

I got it to work (eventually).

The following sub did the trick:

Private Sub Create_PDF_Click()

Dim myPath As String
Dim strReportName As String

DoCmd.OpenReport "Invoices", acViewPreview

myPath = "C:\Documents and Settings\"
strReportName = Report_Invoices.[Client Organisations_Code] + "-" +
Report_Invoices.Clients_Code + "-" + Report_Invoices.Invoices_Code + "-" +
Format(Report_Invoices.[Invoice Date], "yyyy") + ".pdf"

DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath + strReportName, True
DoCmd.Close acReport, "Invoices"

End Sub

Two caveats:

1. The report needs to be opened before printing.
2. Refer to fields by the same name that the report sees it as. That [Client Organisations].Code was [Client Organisations_Code] in the report.