SendObject VBA (but more elegant)

[Guest]

Just a thought.

As there is no “where†clause on the SendObject event I have come up with
the code below that works fine. It just looks a mess. Has anyone any idea
on how to apply a filter (without filtering the query) on a SendObject that
is a little more elegant than this method. The less code I use the less
there is for me to get wrong LoL

If the Option group = 3 (the e mail icon)
Refresh the form (to load the address into a field)
Open the report in Preview (in a hidden screen)
* - the reason for opening the report is to clone the filter accross to the
next line of code - *
Send the report
Send the report to a CC
Close the hidden report and screen.


If Forms! FormName!OptionGroup = 3 Then

Me.Refresh

DoCmd.OpenReport " REPORTNAME ", acViewPreview, "",
"[PrimaryField]=[Forms]![FormName]![FieldName]", acHidden

DoCmd.SendObject acReport, " REPORTNAME ", "RichTextFormat(*.rtf)",
Forms! FormName!FieldName, Forms!FormName!AnotherFieldName, "", "EmailTitle",
"EmailBody ", False, ""

DoCmd.Close acReport, " REPORTNAME "

End If


This is a mess – but as I said it works fine but if anyone has filtered a
SendObject – without QBF (trying to cut down on the number of queries so QBF
would just add another query – which I’m trying to avoid)

Thanks

 

[Arvin Meyer [MVP]]

I don't know why you are adverse to queries. They are the meat and potatoes
for every database in existence. I prefer to put the criteria directly in
the query which underlies the report. In your case that merely means moving
the line:

[Forms]![FormName]![FieldName]

directly to the query's criteria line.

 

[Guest]

Yep - I suppose you are right. Maybe it's as it's Sunday and I am feeling
lazy. I am do this as a "freebee" for a charity and am trying to create the
whole reports thing in one day.

Oh well it was just an idea. You know when you look at some code and think
"yes it works ...but " it just look a mess.

Yet another query it is then.

Thanks for the input.


--
Wayne
Manchester, England.

I don't know why you are adverse to queries. They are the meat and potatoes
for every database in existence. I prefer to put the criteria directly in
the query which underlies the report. In your case that merely means moving
the line:

[Forms]![FormName]![FieldName]

directly to the query's criteria line.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Just a thought.

As there is no "where" clause on the SendObject event I have come up with
the code below that works fine. It just looks a mess. Has anyone any
idea
on how to apply a filter (without filtering the query) on a SendObject
that
is a little more elegant than this method. The less code I use the less
there is for me to get wrong LoL

If the Option group = 3 (the e mail icon)
Refresh the form (to load the address into a field)
Open the report in Preview (in a hidden screen)
* - the reason for opening the report is to clone the filter accross to
the
next line of code - *
Send the report
Send the report to a CC
Close the hidden report and screen.


If Forms! FormName!OptionGroup = 3 Then

Me.Refresh

DoCmd.OpenReport " REPORTNAME ", acViewPreview, "",
"[PrimaryField]=[Forms]![FormName]![FieldName]", acHidden

DoCmd.SendObject acReport, " REPORTNAME ", "RichTextFormat(*.rtf)",
Forms! FormName!FieldName, Forms!FormName!AnotherFieldName, "",
"EmailTitle",
"EmailBody ", False, ""

DoCmd.Close acReport, " REPORTNAME "

End If


This is a mess - but as I said it works fine but if anyone has filtered a
SendObject - without QBF (trying to cut down on the number of queries so
QBF
would just add another query - which I'm trying to avoid)

Thanks