changing file name upon sending with sendobject

[Jean-Paul De Winter]

Hi,
I posted this question in the formcoding and reports newsgroup of access and
got the advice to ry to get my problems solved in this forum..
Sorry if I got the wrong advice, but this is the problem:

I send a generated report through email as a snp file
I noticed the name of the snp file I send is the same as what is written
in the report Caption property
At this moment I wrote:
planning voor gegeven periode
So the send file is: planning voor de gegeven periode.snp

This is the name for all reports I send.

I would like the filename to contain more details so I changed the
report-property into:

"planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer] & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24]

I hoped to get a filename like:

planning voor gegeven periode_12_van_JPDW.snp

But alas I get:
"planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer] & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24].snp
as a filename

What to do?

I tried to delet what is entered in the caption setting and adding a few
lines of code

Private Sub Report_Activate()
Me.Caption = "Algemeen overzicht week " &
[Forms]![bedrijven]![weeknummer]µ
End Sub

but again... problem still not solved

Any idea?
Thanks

JP

 

[Alex Dybenko]

Hi,
try the following:
declare a public variable strReportName

set it to desired report name:
strReportName="planning voor gegeven periode_12_van_JPDW"

close a report if it opened
then run docmd.sendobject

then clear strReportName=""

and in report's open event add:

if len(strReportName)>0 then
me.caption=strReportName
end if

hope it will work

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Jean-Paul De Winter]

I'll try that one..

Sorry, may sound dumb but what do you mean with

then clear strReportName=""

Thank you very much for your kind reply
JP

Hi,
try the following:
declare a public variable strReportName

set it to desired report name:
strReportName="planning voor gegeven periode_12_van_JPDW"

close a report if it opened
then run docmd.sendobject

then clear strReportName=""

and in report's open event add:

if len(strReportName)>0 then
me.caption=strReportName
end if

hope it will work

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi,
I posted this question in the formcoding and reports newsgroup of access
and got the advice to ry to get my problems solved in this forum..
Sorry if I got the wrong advice, but this is the problem:

I send a generated report through email as a snp file
I noticed the name of the snp file I send is the same as what is written
in the report Caption property
At this moment I wrote:
planning voor gegeven periode
So the send file is: planning voor de gegeven periode.snp

This is the name for all reports I send.

I would like the filename to contain more details so I changed the
report-property into:

"planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer] & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24]

I hoped to get a filename like:

planning voor gegeven periode_12_van_JPDW.snp

But alas I get:
"planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer] & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24].snp
as a filename

What to do?

I tried to delet what is entered in the caption setting and adding a few
lines of code

Private Sub Report_Activate()
Me.Caption = "Algemeen overzicht week " &
[Forms]![bedrijven]![weeknummer]µ
End Sub

but again... problem still not solved

Any idea?
Thanks

JP

 

[Alex Dybenko]

Hi,
I mean that you set strReportName to empty string, and when you open your
report in normal way - it will not change its caption

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I'll try that one..

Sorry, may sound dumb but what do you mean with

then clear strReportName=""

Thank you very much for your kind reply
JP

Hi,
try the following:
declare a public variable strReportName

set it to desired report name:
strReportName="planning voor gegeven periode_12_van_JPDW"

close a report if it opened
then run docmd.sendobject

then clear strReportName=""

and in report's open event add:

if len(strReportName)>0 then
me.caption=strReportName
end if

hope it will work

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi,
I posted this question in the formcoding and reports newsgroup of access
and got the advice to ry to get my problems solved in this forum..
Sorry if I got the wrong advice, but this is the problem:

I send a generated report through email as a snp file
I noticed the name of the snp file I send is the same as what is written
in the report Caption property
At this moment I wrote:
planning voor gegeven periode
So the send file is: planning voor de gegeven periode.snp

This is the name for all reports I send.

I would like the filename to contain more details so I changed the
report-property into:

"planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer] & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24]

I hoped to get a filename like:

planning voor gegeven periode_12_van_JPDW.snp

But alas I get:
"planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer] & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24].snp
as a filename

What to do?

I tried to delet what is entered in the caption setting and adding a few
lines of code

Private Sub Report_Activate()
Me.Caption = "Algemeen overzicht week " &
[Forms]![bedrijven]![weeknummer]µ
End Sub

but again... problem still not solved

Any idea?
Thanks

JP

 

[Jean-Paul]

doesn't work:

In the module I wrote:

Public strReportName As String

In the OnOpen property of the form I wrote:

Private Sub Form_Open(Cancel As Integer)
strReportName = "planning voor gegeven periode_12_van_JPDW"
............

On this form there is a pushbutton.
This is the code:

Private Sub Knop247_Click()
On Error GoTo Err_Knop247_Click
Me!verstuurd = "J"

DoCmd.SendObject acSendReport, "Te bezoeken2", acFormatSNP,
MP_Setup.verkopers, , , "Algemeen overzicht week " & Me!weeknummer & "
van " & MP_Setup.gebruiker, "", False
Exit_Knop247_Click:
Exit Sub
Err_Knop247_Click:
Select Case Err.Number
Case 2293
Resume Next
Case Else
MsgBox Err.Number & Err.Description
Resume Exit_Knop247_Click
End Select
End Sub

In the onOpen event of the report I wrote:

Private Sub Report_Open(Cancel As Integer)
If Len(strReportName) > 0 Then
Me.Caption = strReportName
End If
End Sub

Where to add the strReportName="" ?
I have the feeling it has to do with the OnOpen event of the report.
Actually I don't really open the report.. When I send it through the
SendOjcet command I don't see it open before it is send.

This all is very confusing.
Looks like a very basic problem but hard to solve.
Thanks

 

[Alex Dybenko]

Hi,
I would try the 2 things:
1. step through the code in Report_Open at check if it really changing the
caption
2. if no - then I would output report to a hard drive, then rename it and
then email it, in this case you can use outlook object model to create new
mail, add attachment, etc. More work, but you will get better control

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Jean-Paul]

Hi,
Sorry for the late reply... been away for a few days...

I placed a stop in the code to see if the code realy ran, but no..

Private Sub Report_Open(Cancel As Integer)
If Len(strReportName) > 0 Then
Me.Caption = strReportName
End If
End Sub

the idea is to send a snp file with one push on a button... avoid
"forgetting" to send a file... so, your second suggestion is not an option.
It surprises me this seems to be complicated...

Hope you will be able to help...

Thanks
JP

 

[Jean-Paul]

Thanks for your kind reply...
Did what you suggested...
the send file name is exacly what you suggested:

="planning voor gegeven periode_" &
Eval("[Formulieren]![bedrijven]![weeknummer]") & "_van_" &
Eval("[Formulieren]![Hoofdmenu]![Tekst24]")").snp

So.... not correct
JP

Try using

="planning voor gegeven periode_" &
Eval("[Formulieren]![bedrijven]![weeknummer]") & "_van_" &
Eval("[Formulieren]![Hoofdmenu]![Tekst24]")")

Don't do this in VBA, just go to the report in design mode, bring up the
report property dialog box and set the caption to that value. From my
experience, when you reference form control values in a form or report
property field you need to use the Eval function, and as such you need to
preface the value with a "=". Pretty sure that should do it.

Hi,
Sorry for the late reply... been away for a few days...

I placed a stop in the code to see if the code realy ran, but no..

Private Sub Report_Open(Cancel As Integer)
If Len(strReportName) > 0 Then
Me.Caption = strReportName
End If
End Sub

the idea is to send a snp file with one push on a button... avoid
"forgetting" to send a file... so, your second suggestion is not an option.
It surprises me this seems to be complicated...

Hope you will be able to help...

Thanks
JP

Hi,
I would try the 2 things:

[quoted text clipped - 4 lines]

create new mail, add attachment, etc. More work, but you will get better
control

 

[Jean-Paul]

Sorry, doesn't work.
In the report property Open and Activate I wrote:

Private Sub Report_Activate()
Me.Caption = "planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer] & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24]
End Sub

The final output is a report with the report-name as file-name.

The report is created when a pushbutton is clicked.
following code is ran:

DoCmd.SendObject acSendReport, "Te bezoeken2", acFormatSNP,
MP_Setup.verkopers, , , "Algemeen overzicht week " & Me!weeknummer & "
van " & MP_Setup.gebruiker, "", False

the sending of the report runs automatically. After running the above
code, the user opens Outlook, clicks send and the report is correctly
send to the correct persons... but always with the same filename

Still hope you find the solution for this very annoying problem

Thank you so much
JP, Belgium

FYI I put an extraneous ')' in that - there shouldn't be any parentheses.

WHoops, sorry about that - I use form values in form and report properties
frequently, often with text strings concatenated as well, but I've always
used it for a controls ControlSource property - guess it doesn't work for a
caption, just tested it myself. It should definitely work if you do it in VBA
code (I always try to avoid writing code if I can just put it into a property
instead, that's why I suggested putting it into the report property). In the
Open event of the report, try putting

Me.Caption = "planning voor gegeven periode_" &
[Formulieren]![bedrijven]![weeknummer]") & "_van_" &
[Formulieren]![Hoofdmenu]![Tekst24]

You don't need the Eval function for this. I tested this myself just now and
it worked for me, so hopefully this will work for you too. Make sure you have
the form control values in the right format! I'm assuming that 'Formulieren'
is the equivalent of 'Forms'. Set a break point on that statement, put your
cursor over the form values to make sure they are right when it breaks there,
step to the next statement and make sure me.caption has the right value.

Thanks for your kind reply...
Did what you suggested...

[quoted text clipped - 12 lines]

create new mail, add attachment, etc. More work, but you will get better
control