5

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?

Community
  • 1
  • 1
Zaid
  • 36,680
  • 16
  • 86
  • 155
  • 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 Feb 19 '10 at 15:54
  • @Philippe: The macro works fine, the issue is with getting VBA to recognize the fields. – Zaid Feb 20 '10 at 08:46
  • 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 Feb 20 '10 at 08:52
  • @Philippe: issue resolved (see my post below). Thanks for the help anyway. – Zaid Feb 20 '10 at 10:29

2 Answers2

15

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.
Zaid
  • 36,680
  • 16
  • 86
  • 155
  • 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. – David-W-Fenton Feb 21 '10 at 05:56
  • 1
    @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]...`. – Zaid Feb 21 '10 at 06:24
  • 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. – David-W-Fenton Feb 22 '10 at 00:31
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.
JonH
  • 32,732
  • 12
  • 87
  • 145
  • @JonH: I made the relevant changes, clicking on the button does nothing. No errors, nothing. – Zaid Feb 19 '10 at 15:24
  • 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 ? – JonH Feb 19 '10 at 15:25
  • 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. – David-W-Fenton Feb 19 '10 at 20:21
  • @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' – Zaid Feb 20 '10 at 08:42