Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2010 - report - dynamically change report file name

Posted on 2013-05-24
8
Medium Priority
?
3,166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

715 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