Link to home
Start Free TrialLog in
Avatar of MonkeyPie
MonkeyPieFlag for Australia

asked on

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?
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

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
Avatar of MonkeyPie

ASKER

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.
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
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.
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
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
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?
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial