Solved

Access 2010 - report - dynamically change report file name

Posted on 2013-05-24
8
2,825 Views
Last Modified: 2013-06-19
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?
0
Comment
Question by:MonkeyPie
  • 5
  • 3
8 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39196030
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
 

Author Comment

by:MonkeyPie
ID: 39196137
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39196139
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:MonkeyPie
ID: 39206233
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39207203
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39207207
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
 

Author Comment

by:MonkeyPie
ID: 39207378
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
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 39209706
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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question