Report Caption

[vbbbmom]

I am using Access 2010. I would like to know if there is a way, when a report is opened, that the name of the report can be based off of a field within the report as well as a standard name.

For example, when I open the service report for customer Johnson, I would like when I export the report (via email or save as pdf) that the report name be Johnson Service Report, rather than Service Report. This name would also change when I open the service report for cusotmer Wilcox, the report name would then be Wilcox Service Report. Is this possible?

This way when I save the pdf to the digital file I do not have to rename it, as well as if I am sending the report via email the specific customer will be noted in the report name without having to change it manually.

Any help with this would be greatly appreciated!

Thank you!

 

[Ingeneeus]

I'll second this (although I'm using Access 2003 with SP3).
I've got a report -- let's say the report name is "Customer Request Report" -- When I run the report from my Form, it generates a .pdf with no problem (I have the "printer" selected as "AdobePDF" in Page Setup).

Like vbbbmom up there, however, the .pdf writer defaults to the report name "Customer Request Report.pdf" when it creates the file. I've made some progress: I've discovered the Caption field in the Report Properties, so I can change the default name to "Customer Request" (minus the "Report") but what I really want to do is have the name be dynamic: "Customer Report # xyz", with xyz being the invoice number from the [OrderNumber] textbox in the report (the report Record Source is a query named, imaginatively enough, "Customer Request Query")

I've tried doing something like Customer Request & [OrderNumber] in the Caption field, both with quotes around "Customer Request" and without, but then the report title saves as "Customer Request & [OrderNumber].pdf" I've read some advice that I should try to write some recaptioning code ("me.caption=Customer Request, etc) in the On Activate or On Open properties, but that doesn't seem to work either.

What am I missing?

 

[vbaInet]

To answer vbbbmom's old question (for the benefit of others) you can use the DoCmd.OutputTo command in Acces 2007 and above to export to pdf and give it the filename you desire.

Ingeneeus, in your case it's not an Access problem but the fact that you are exporting to an external facility and sadly Access 2003 doesn't have the command that allows you to save to pdf format. What you can do is to export to pdf using your utility and after that's done, use the Name function (in VBA) to change your file name. It works like this:

Name "[COLOR=Red]Old path to file[/COLOR]" As "[COLOR=Red]New Path to file[/COLOR]"

 

[Ingeneeus]

Thanks for your answer, vbaInet. I'm afraid, however, that the brevity of your solution requires a deeper understanding of Access (and VBA) than I possess.

Could you perhaps clarify when -- or how -- to employ the your code solution? When you say export to .pdf using my utility, are you referring to using the "print to .pdf" function that I stated I am using?

When -- and where -- should I put the code you supplied? And should it be something like like Name "Customer Request Report.pdf" As "Customer Request #" & [Forms]![View Orders]![OrderNumber]?

Thanks,

 

[Ingeneeus]

Hey! I just got it. The caption via code thing does work after all. Don't know what I was doing wrong yesterday, but here's what I've got:

I opened the report in Design mode, opened up the Properties window, and selected Report from the pull-down. I opened the Event tab, and in On Open, I used the following code as an Event Procedure:
Private Sub Report_Open(Cancel As Integer)
Me.Caption = "Customer Request " & [Forms]![View Orders]![OrderNumber]
End Sub

Thanks again for your suggestion; as a novice, I really appreciate any and all assistance.

 

[Ingeneeus]

OK, I don't got it. It worked once, and now it doesn't.
Clearly, I'm still missing something.

 

[vbaInet]

Ah, I see. It's interesting to see that the pdf printer is using the Caption property of the report. In that case you need to,

Open in design view > change the Caption > close the report with the acSaveYes parameter > print to pdf

But I think you are better off using Stephen Leban's save to pdf function. Here's the link:

http://lebans.com/reporttopdf.htm

It enables you save a report to pdf without needing to pre-opening the report before sending to pdf.

To answer your other question, the Name statement is used like this:

Name "C:\The Directory\FileName.pdf" As "C:\The Directory\NewFileName.pdf"

That's what I meant by full path.

 

[Ingeneeus]

Thanks for responding again, vba.
I apologize for not picking this up quickly. This, I suppose, is what happens when someone who doesn't have an Access background is tasked with making something happen in Access :-|

I'll try to if I can get a handle on this.

Here's how I'm presently set up. I open the report in question from my Form as follows: DoCmd.OpenReport "Customer Request Report", acViewNormal, , , acHidden.
As I understand, this
1) Opens my report [OpenReport]
2) Sends the report to its default printer (in this case the .pdf printer) [acViewNormal]
3) With no Filters [,]
4) And no Conditions [,]
5) Without displaying it on the screen [acHidden]

So . . . if I'm following you (and based on my limited understanding of the OpenReport command), I should change this to DoCmd.OpenReport "Customer Request Report", acViewDesign, , , acHidden, which will open the report in Design so I can change the caption. I would then use the Me.Caption = "Customer Request " & [Forms]![View Orders]![OrderNumber] code, which would give me the name I want. I would then insert a DoCmd.Printout statement, which would send the report -- with the new name -- to the .pdf printer since that is the report's default printer?

I hope I'm getting it . . .

Thanks also for the suggestion about Stephan Leban's application -- I'm presently operating out of an invoicing system that we didn't create, so I'm not sure it would play nicely with Mr. Leban's pdf tool (this thing seems to be pretty finicky). I may try it if I can't get your suggestions to work.

Thanks,

 

[vbaInet]

I mentioned the exact steps above. You missed out the most crucial part, saving it and closing it before issuing the PrintOut or Print command.

I doubt that Stephen Leban's tool will interfer with anycode. All you are doing is utilising the code.

 

[Ingeneeus]

I think I'm almost there. Here's what I've got now:
DoCmd.OpenReport "Customer Request Report", acViewDesign, , , acHidden
Me.Caption = "Invoice " & [Forms]![View Orders]![OrderNumber]
DoCmd.Close acReport, "Customer Request Report", acSaveYes
Do.Cmd.Printout.
The problem here is that 1) The print job is sent to my the application's default laser printer, not the .pdf printer, and 2) what prints is an image of the Form I'm using to launch the report, not the report itself. Access 2003 doesn't seem to have a DoCmd.Print code, only DoCmd.Printout. I'm not sure how to tell Access what I want to "print"
I thought maybe I needed to put the line DoCmd.OpenReport "Customer Request Report", acViewNormal, , , acHidden in after the Save line )instead of DoCmd.Print, but that just puts me right back in the original loop, so that can't be it.

I feel like I must be close here, but don't know enough to finish it off . . .

 

[vbaInet]

You are going to end up back to using Leban's code. If your pdf printer doesn't have command line arguments then you're going to end up getting a dialog box asking to select which folder to save in ... etc. Even though you can set the printer in code and print the correct report you still need a pdf printer that has command line options.

Just try Leban's code.

 

[Ingeneeus]

I'll give leban a try. Thanks for all your help!

 

[vbaInet]

No problem

Just for your own enlightenment:

DoCmd.Close acReport, "Customer Request Report", acSaveYes
...open the report here and make it hidden using OpenReport with acHidden...
... set the report's printer device using the report's Printer method ...
... print using the report's Print method...

 

[Ingeneeus]

Oh . . . My.

I know this is probably closed, and I know that this solution probably doesn't apply to anyone else but me, but I feel compelled to share just in case there's somebody out there in this situation.

What a good night's sleep and a little stubbornness will do.

DoCmd.OpenReport "Customer Request Report", acPreview, , , acNormal
Me.Caption = "Customer Request " & [Forms]![View Orders]![OrderNumber]
DoCmd.PrintOut
DoCmd.Close

Opening it in acPreview lets the "caption" code change the caption without sending it to the printer. Since the Report is still open, DoCmd.Printout "chooses" to print the Report, rather than the open Form from which I called the Report (see above posts). Because the default "printer" for the Report is the .pdf printer, I get a .pdf named "Customer Request xyz.pdf"
Since (and I'm now guessing here) the Report is the "top" item open, DoCmd.Close closes the Report, and away I go.

Again, thanks so much for all your help, vbaInet! I really wouldn't have fumbled over this solution without your input.

 

[vbaInet]

Glad to see you made good progress there.

Just a few pointers:

* If you're going to create several pdfs by calling the code iteratively, you will fall into problems eventually.
* You should have called the report's Print method instead of the generic PrintOut method, i.e. Reports("ReportName").Print
* I mentioned you should open the report in hidden mode and print. That way you would create a pdf without the user seeing things happening on the screen.

 

[Ingeneeus]

Thanks for the final pointers. I'll make the changes in the print method, and will switch back to hidden mode now that I know this works.
Have a good week!

 

[vbaInet]

You too!