Access 2010 - report - dynamically change report file name

I have a report rptInvoice.  When report is printed the client wants to see the invoice  number in the file name in a print queue, or when he saves as PDF.  
The form property caption is set to "Invoice" but in the open event I have:

me.caption=Forms!FrmInvoice!InvNo & " Invoice"

For the name in the print queue, only the report caption property is used - the vba is ignored.

So, I see "Invoice" in print queue, not "Ajax 222 Invoice" for example.

How can I change the report caption so it appears in the print queue how I want?
MonkeyPieAsked:
Who is Participating?
 
Kelvin SparksCommented:
OK, Office 2010 - good - no addin needed.

As I understand, you want an option to output a report to pdf with your custom report name as the file name - correct?

The first thing you need is code to create the filename and path.

I'd use something like:

Dim sPDFName as String

sPDFName = "C:\MyFiles\" & Forms!FrmInvoice!InvNo & " Invoice.pdf"

Once we've done that all we have to do it to output it.

Docmd.OutputTo acOutputReport,"Invoice",acformatpdf, sPDFName, True

This will create the invoice using the name in Forms!FrmInvoice!InvNo using the report Invoice  and output it to C:\MyFiles, then open the pdf in the default pdf viewer.

Kelvin
0
 
Kelvin SparksCommented:
You are best to deal with this as part of the print process. Either by setting the name as you create the pdf - or by renaming it once done. I prefer and recommend the first.

What code are you using to create the PDF?


Kelvin
0
 
MonkeyPieAuthor Commented:
Hi Kelvin,
I don't think your option will work.  

There is a popup up form where the user ticks the reports to print, from a selection of 4 reports.  There is also a combo box with all the available printers in it.  If no printer is selected the report prints directly to to the default printer, but the user has the option to select any available printer.  Sometimes the user will pick PDFCreator or similar, but I have no idea which pdf creator they use - each user may have different app to do this.

I then just use:
docmd.openReport "rptInvoice"

to print report directly to application default printer.

Just to repeat - what I want to change is not just the name of the PDF file, but also what the document name is in any print queue.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Kelvin SparksCommented:
OK, I understand now. The report name is as you say in the properties. Report properties cannot be changed during the report printing run. The only way to do that is to open the report in design mode and set the property (can be done in code) using data selected from their selection options, then close and save the changes, then use the report run as you've indicated.

The "free" pdf printer tools around do not normally allow you to change anything in the VBA, so wouldn't be worth pursuing that.

Won't be the easiest to code, but is possible.

Kelvin
0
 
MonkeyPieAuthor Commented:
Hi Kelvin,
I have had success changing the report name when user presses "PRINT" option, and then selects PDF creator or similar as their printer by using the following:
                'add this invoice number to report name, so it shows in print queue, and PDF file name
                strInvoiceNo = DLookup("InvoiceNo", "BillingInformation", "ID=" & TempVars!CurrentJobBillingID)
                DoCmd.OpenReport "rptInvoice", acViewDesign, , , acHidden
                Reports!rptInvoice.Caption = strInvoiceNo & " Invoice"
                DoCmd.Close acReport, "rptInvoice", acSaveYes

Open in new window

Luckily this works with RunTIme - I didn't think it would!

However, when the report is opened in preview mode (on the screen) I have a shortcut menu (thanks to the great code provided by Edwin Blancovitch
http://blogs.office.com/b/microsoft-access/archive/2009/05/21/how-to-create-a-shortcut-menu-for-a-form-form-control-or-report.aspx

This shortcut menu has an option to save to PDF, as well as print.
Print from this shortcut menu works beautifully with the report name appearing in the print queue as "Job xx Invoice", however if you try to create PDF from this shortcut the report name is rptInvoice.

How can I change the report name in this instance (create PDF from report preview shortcut menu)?

Thank you.
0
 
Kelvin SparksCommented:
OK, what version of Access - from 2007 onward - there is the option to output to pdf - and in that option you get to name the location and filename.

Is essentially
Docmd.OutputTo acOutputReport,rptname,acformatpdf,"c:\temp\test.pdf", True

reptame is the name of your report (don't need to worry about the caption here) , Replace the filestring with your actual filestring including the name of the file you want it to have - normally set that in the lines before and just enter the variable that you want that has those details. The True will open the report in your pdf viewer (as well as saving it), False will not of it after creating it.


Kelvin
0
 
Kelvin SparksCommented:
If Access 2007, then you need the free MS addin for pdf's. Prior to 2007 - its a third party job. There iss a dll in the old website of Stephen Lebans that will do it for you in these cases.


Kelvin
0
 
MonkeyPieAuthor Commented:
I'm using Access 2010 but I am developing for a 64 bit Office Application so are addins OK with 64 bit?  I have no idea where to start with this one.  Can you give me a little more info?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.