Rename dynamic report prior to converting to pdf

[Happy2Bhere]

I currently have a report that works perfect. I have set up properties to convert the report to a pdf file and email it. Wonderful!!!

My issue - I would like to rename the report to data in an underlying query prior to it converting to a pdf and sending it off. I don't want to change the existing report, I simply want to do something like "Save as.pdf"

I'm not sure where to start. I undertsand "where conditions" and I was thinking that I might put a macro in Events prior to Opening the report?

Does anyone have an idea of how to approach this task? I'm not familiar with VB or SQL but I'm happy to do research if pointed in a direction.

I'm using Access 2007.

Any ideas/suggestions are welcomed and appreciated.

Thanks!

 

[Vassago]

I currently have a report that works perfect. I have set up properties to convert the report to a pdf file and email it. Wonderful!!!

My issue - I would like to rename the report to data in an underlying query prior to it converting to a pdf and sending it off. I don't want to change the existing report, I simply want to do something like "Save as.pdf"

I'm not sure where to start. I undertsand "where conditions" and I was thinking that I might put a macro in Events prior to Opening the report?

Does anyone have an idea of how to approach this task? I'm not familiar with VB or SQL but I'm happy to do research if pointed in a direction.

I'm using Access 2007.

Any ideas/suggestions are welcomed and appreciated.

Thanks!

I do the same thing in one of my reports so the output pdf is a user-friendly name. This is what I do using vba in the onclick procedure of a button on the form that opens the target report:

'Copy the report to a new report with a different name within the DB
''''''''''''''''''''''''''''''''''''''''''''''
DoCmd.CopyObject , "Escalation Login Logout " & Format(Me!txt_startdate, "mm-dd-yyyy"), acReport, "rpt_login_logout_Sup"

'Open the new report
''''''''''''''''''''''''''''''''''''''''''''''
DoCmd.OpenReport "Escalation Login Logout " & Format(Me!txt_startdate, "mm-dd-yyyy"), acViewNormal

'When you want to delete the new report name to save space in the db...
''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
DoCmd.DeleteObject acReport, "Escalation Login Logout " & Format(Me!txt_startdate, "mm-dd-yyyy")
On Error GoTo 0

 

[Happy2Bhere]

Ohh...that's clever! I understand the code you wrote. Thank you. Although, I'm not familiar with VBA, I'll give it a shot.

I aslo noticed that changing the caption on the report title will show up in the email as the "save as.pdf".

Got any ideas of how to write a macro to change the caption on the report?


Thanks for the help. ~Very much appreciated!

 

[Vassago]

Ohh...that's clever! I understand the code you wrote. Thank you. Although, I'm not familiar with VBA, I'll give it a shot.

I aslo noticed that changing the caption on the report title will show up in the email as the "save as.pdf".

Got any ideas of how to write a macro to change the caption on the report?


Thanks for the help. ~Very much appreciated!

You mean the name of the report exports as "save as.pdf"? I've always had it output as the report name, which is why I renamed it to begin with. For example, if you ran the code above today, it would look like "Escalation Login Logout 04-01-2010.pdf". The txt_startdate is a text box on my form that they fill in. I just format it in code so it can be saved to a file name. (Windows doesn't allow / in the filename for obvious reasons. )

 

[Happy2Bhere]

Yes, If you change the caption on the report it will export as the caption name.pdf as well as load the pdf into outlook and send off to it's designated email address and/or sharepoint location.

I tried it. Simply change the caption name on the report. Save the report. Then send as a pdf. If the report doesn't have a caption name it will default to the reportname.

 

[Vassago]

Yes, If you change the caption on the report it will export as the caption name.pdf as well as load the pdf into outlook and send off to it's designated email address and/or sharepoint location.

I tried it. Simply change the caption name on the report. Save the report. Then send as a pdf. If the report doesn't have a caption name it will default to the reportname.

Awesome, glad you got it sorted out.

 

[Happy2Bhere]

I havent' figured it out yet.

I'm still trying to figure out how to write the macro so it will automatically update the caption when the report opens.

Thanks for the support!

 

[Vassago]

I havent' figured it out yet.

I'm still trying to figure out how to write the macro so it will automatically update the caption when the report opens.

Thanks for the support!

Why have a caption at all? That's what I'm getting at, if you leave the caption blank, it will always be the report name. It will also show the report name in the title bar with a blank caption. Just name the report what you want it to be (what you want them to see) through the code I posted above.

I think you're going to find a problem with setting the Caption because the report must be opened in order to set this option through vba, which won't work because it's already opened and visible and won't change on the title bar, thus in the save as dialog.