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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.