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?

Solution 1

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.

Solution 2

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.

Comments

[Zaid]

@JonH: I made the relevant changes, clicking on the button does nothing. No errors, nothing.

[LoganS]

You did debug what strReportName displays? Also don't you need a pdf distiller / printer to print to PDF. You cannot expect DoCmd.OutputTo to really create a pdf without a writer do you ?

[David-W-Fenton]

There's a downloadable add-in to do PDF generation in Office 2007. It is required for the acFormatPDF parameter to work. That is, Access 2007 without that add-in installed will not be able to output to PDF via the DoCmd.OutputTo command.

[Zaid]

@David: I already have the add-in installed. @JonH: Now it can't find the field [Client Organisations].Code. Me.[Client Organisations].Code doesn't work either. Run-time error '2465'

[David-W-Fenton]

Is [Client Organisations] as subform control? If so, the usual syntax to refer to a control or field in the form embedded in the subform control is Me![Client Organisations].Form!Code. It would have been much easier to offer you an answer if you'd specified that [Client Organisations] was a subform. If it's not, then I don't understand why your solution works.

[Zaid]

@David : It's not. [Client Organisations].Code was a field that the report saw as [Client Organisations_Code] because of the SELECT query that the report is based on: SELECT [Client Organisations].Code AS [Client Organisations_Code]....

[David-W-Fenton]

You should avoid that kind of thing in your recordsources. I would say. That is, having the same field name from multiple tables is an error in the SQL statement, either because it's redundant (it's a PK/FK relating two tables) or because it's a design error (fields that don't store the same data should not be given the same name in multiple tables). Also, putting spaces in table/field names is inadvisable. Camel case without spaces is just as readable and quite a bit easier to work with.