Email SubForm

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thank you to Anyone who attempts an answer on this one it is a long one

I have an events managment database based on the microsoft template but
highly modified and have for two months been trying to make it coax it to
send a confirmation of booking email to participants from a form/subform

the main form is called Attendees
the sub form is AttendeesSubform (original) with the field [RegistrationFee]

i can get an email to send with the following code

oCmd.SendObject _
acSendQuery, "Email_query", acFormatTXT, _
[EmailName], _
Nz([PDN_Email], ""), _
Nz([Managers_Email], ""), _
"Confirmation of registration on Course", _
"Dear " & [AttendeeFirstName] & ", " & _
"I can confirm you have a place on the" & " " & DLookup("[EventName]",
"Events", _
"[EventName] = '" & _
Me![Attendees Subform]![EventName] & "'") & "." & _
" Details of dates and Costs can be found in the enclosed attachment" & _
" I will contact you near the time to confirm time and venues," & _
" The outstanding balance " & _
" Will be invoiced for near the course start date" & _
" Thanks Phil", _
True

but as you can see i have to use a query to get info from the sub form in
the email

i would like the email to say "the outstanding balance of [RegistrationFee]
will be invoiced for nearer the time"

the problem i have is i cannot find a way to refer to the subform field, the
subform data is collected using a select query if that makes any difference

i previously got the info for the course to work but if put in the reg fee
field it comes up with an error the reg fee is calculated using

=[RegistrationFee]+CLng((([SalesTaxRate]*[RegistrationFee])*100))/100

if this makes a difference

i hope someone can help me

thanks

Phil
 
First, a somewhat useful answer:

To refer to a subform's field from a Main form:
Me![SubformControlName}.Form![ControlName]

Second, a shameless plug for a great product:

Total Access Emailer: http://fmsinc.com/products/emailer/index.asp

Total Access Emailer leverages the power of Microsoft Access and email so
you can generate personalized email for everyone in your table or query.
Easily send email from Access in text or HTML format with attachments. Use
field values in your email subject and message to create unique,
personalized emails for each recipient. You can even maintain an audit trail
to know who was contacted when
 
Steve Thanks a lot, that worked great

thanks

Phil

[MVP] S.Clark said:
First, a somewhat useful answer:

To refer to a subform's field from a Main form:
Me![SubformControlName}.Form![ControlName]

Second, a shameless plug for a great product:

Total Access Emailer: http://fmsinc.com/products/emailer/index.asp

Total Access Emailer leverages the power of Microsoft Access and email so
you can generate personalized email for everyone in your table or query.
Easily send email from Access in text or HTML format with attachments. Use
field values in your email subject and message to create unique,
personalized emails for each recipient. You can even maintain an audit trail
to know who was contacted when

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Phil said:
Thank you to Anyone who attempts an answer on this one it is a long one

I have an events managment database based on the microsoft template but
highly modified and have for two months been trying to make it coax it to
send a confirmation of booking email to participants from a form/subform

the main form is called Attendees
the sub form is AttendeesSubform (original) with the field
[RegistrationFee]

i can get an email to send with the following code

oCmd.SendObject _
acSendQuery, "Email_query", acFormatTXT, _
[EmailName], _
Nz([PDN_Email], ""), _
Nz([Managers_Email], ""), _
"Confirmation of registration on Course", _
"Dear " & [AttendeeFirstName] & ", " & _
"I can confirm you have a place on the" & " " & DLookup("[EventName]",
"Events", _
"[EventName] = '" & _
Me![Attendees Subform]![EventName] & "'") & "." & _
" Details of dates and Costs can be found in the enclosed attachment" &
_
" I will contact you near the time to confirm time and venues," & _
" The outstanding balance " & _
" Will be invoiced for near the course start date" & _
" Thanks Phil", _
True

but as you can see i have to use a query to get info from the sub form in
the email

i would like the email to say "the outstanding balance of
[RegistrationFee]
will be invoiced for nearer the time"

the problem i have is i cannot find a way to refer to the subform field,
the
subform data is collected using a select query if that makes any
difference

i previously got the info for the course to work but if put in the reg fee
field it comes up with an error the reg fee is calculated using

=[RegistrationFee]+CLng((([SalesTaxRate]*[RegistrationFee])*100))/100

if this makes a difference

i hope someone can help me

thanks

Phil
Click to expand...
Click to expand...
 
Back
Top