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

5
votes

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?

2
vbams-accessreportsave-as
this 'can't find the field' error usually appears when something is wong somewhere in the libraries: version issue, unrecognised parameter, etc. I guess @jonH is right: you are missing something outside VBA to have your PDF reports printed. – Philippe Grondier
@Philippe: The macro works fine, the issue is with getting VBA to recognize the fields. – Zaid
in this case try a few 'debug.print' in the immediate window and test each one of the parameters of your file name to find which one is faulty: ? debug.print [Client Organisations].Code ? debug.print Clients.Code etc – Philippe Grondier
@Philippe: issue resolved (see my post below). Thanks for the help anyway. – Zaid

2 Answers

15
votes

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.
1
votes

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.