Emailing from a form using recursive query results

[benforum via AccessMonster.com]

I am trying to create a button on a form (form name is [BG Paper Info]), when
cliking the button it should do the following things:
1. Activate a query [BG Email from AE Code] that uses a Control [AE Code]
that appears on the form (which can change from record to record), in order
to generate an Email address ([EmailAddress] in the query)to which to email a
report.

The report is generated using another query [BG Reminder letter] that
contains a memo field with the text of the letter, the text depends on a code
[Reminder Type] that appears in the form.

The two queries and the report operate correctly and produce the desired
outputs.

2. I try to use sendobject to email the report to the address in [BG Email
from AE Code]![EmailAddress] and nothing is emailed and I receive error
messages.

I will appreciate it if someone could tell me how to do it right.

A seperate issue is: How do I insert the text of the report in the message so
it is not an attachement, the text is much longer than the 255charcter limit.
I use ACCESS 2007.

-Ben

 

[Dale Fye]

Ben,

You didn't say whether the [AE Code] field in your form is bound to the
forms recordset, or whether it is just a text box for entering a code, so
I'll assume the latter. If it is the former, post back, as the code will be
slightly different. When I have this situation, I generally do something
like the following (this assumes that the recipients email address is in the
query [BG Email from AE Code]:

Private sub cmd_SendMultiple_Click

Dim rs as DAO.recordset

Set rs = currentdb.openrecordset "[BG Email from AE Code]"
While not rs.eof
me.txt_AE_Code = rs("AE Code")
docmd.SendObject acSendReport, "ReportName", , rs("AE_Code_Email"),
, , "Subject", "Message"
rs.movenext
Wend

rs.close
set rs = nothing
End sub

Another way that I have been doing this lately, is rather than referring to
a control on a form, I create a function that allows me to store or retrieve
a value. Whenever the value of the control changes, I push that value to
the function. I then refer to this function in my queries rather than the
forms control. This allows me the flexability of using the same query in
multiple places within my application. You will need to put this function
in a code module rather than in the code behind a form. In your case the
function might look like:

Public Function fnAECode(Optional SomeValue as Variant = NULL) as Variant

'This function will return a zero (the default for a variable declared
as an integer)
'if no value has been passed to it previously
Static myValue as Integer 'I would actually type this as whatever
data type your [AE Code] value is

IF NOT isnull(SomeValue) then myValue = SomeValue
fnAECode = myValue

End Function

If you have a bound form, in the forms current event you would pass the
value of the [AE Code] to the function like:

Private sub Form_Current

Call fnAECode(me.txt_AECode)

End Sub

You would also need to pass the value to the function if you manually change
(AfterUpdate) the value in the [AE Code] textbox.

Then in your query, where you had something that looks like:

SELECT * FROM yourTable WHERE [AE Code] = forms!formname.Controlname

You would now write:

SELECT * FROM yourTable WHERE [AE Code] = fnAECode()

Using this method, you would replace the line in the original function above
that reads:

me.txt_AE_Code = rs("AE Code")

with

Call fnAECode(rs("AE Code"))

HTH
Dale

I am trying to create a button on a form (form name is [BG Paper Info]),
when
cliking the button it should do the following things:
1. Activate a query [BG Email from AE Code] that uses a Control [AE Code]
that appears on the form (which can change from record to record), in
order
to generate an Email address ([EmailAddress] in the query)to which to
email a
report.

The report is generated using another query [BG Reminder letter] that
contains a memo field with the text of the letter, the text depends on a
code
[Reminder Type] that appears in the form.

The two queries and the report operate correctly and produce the desired
outputs.

2. I try to use sendobject to email the report to the address in [BG Email
from AE Code]![EmailAddress] and nothing is emailed and I receive error
messages.

I will appreciate it if someone could tell me how to do it right.

A seperate issue is: How do I insert the text of the report in the message
so
it is not an attachement, the text is much longer than the 255charcter
limit.
I use ACCESS 2007.

-Ben

 

[benforum via AccessMonster.com]

Dale,

Thank you for your help.
I realize that I had not supplied some important data. Here it is.
1. The form is used by a human being who has on it one record of the data
base. The record contains in it the [AE Code] and it is laso listed on the
form.
2. The human decides if he wants to press the commmand button. and once
pressed a SINGLE record corresponding to what is listed on the form is used
to generate the text in the message.
3. A query that uses the [AE Code] retrieves from anothr relation the
emailaddress corresponding to [AE Code], and other data in the form is used
to generate the text message. A single Emailaddress is generated for this
letter.
4. The message is sent and the user advances to the next record repeating the
same steps.

I noticed that, when the user advances from record to record, the
Emailaddress does not change.
The emailaddress format is not a text string in spite of being defined as
plain text in the DB.

I hope that the above points alrify the situation.

Best wishes,
Ben

Ben,

You didn't say whether the [AE Code] field in your form is bound to the
forms recordset, or whether it is just a text box for entering a code, so
I'll assume the latter. If it is the former, post back, as the code will be
slightly different. When I have this situation, I generally do something
like the following (this assumes that the recipients email address is in the
query [BG Email from AE Code]:

Private sub cmd_SendMultiple_Click

Dim rs as DAO.recordset

Set rs = currentdb.openrecordset "[BG Email from AE Code]"
While not rs.eof
me.txt_AE_Code = rs("AE Code")
docmd.SendObject acSendReport, "ReportName", , rs("AE_Code_Email"),
, , "Subject", "Message"
rs.movenext
Wend

rs.close
set rs = nothing
End sub

Another way that I have been doing this lately, is rather than referring to
a control on a form, I create a function that allows me to store or retrieve
a value. Whenever the value of the control changes, I push that value to
the function. I then refer to this function in my queries rather than the
forms control. This allows me the flexability of using the same query in
multiple places within my application. You will need to put this function
in a code module rather than in the code behind a form. In your case the
function might look like:

Public Function fnAECode(Optional SomeValue as Variant = NULL) as Variant

'This function will return a zero (the default for a variable declared
as an integer)
'if no value has been passed to it previously
Static myValue as Integer 'I would actually type this as whatever
data type your [AE Code] value is

IF NOT isnull(SomeValue) then myValue = SomeValue
fnAECode = myValue

End Function

If you have a bound form, in the forms current event you would pass the
value of the [AE Code] to the function like:

Private sub Form_Current

Call fnAECode(me.txt_AECode)

End Sub

You would also need to pass the value to the function if you manually change
(AfterUpdate) the value in the [AE Code] textbox.

Then in your query, where you had something that looks like:

SELECT * FROM yourTable WHERE [AE Code] = forms!formname.Controlname

You would now write:

SELECT * FROM yourTable WHERE [AE Code] = fnAECode()

Using this method, you would replace the line in the original function above
that reads:

me.txt_AE_Code = rs("AE Code")

with

Call fnAECode(rs("AE Code"))

HTH
Dale

I am trying to create a button on a form (form name is [BG Paper Info]),
when

[quoted text clipped - 27 lines]

-Ben