Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Email SubForm

Discussion in 'Information Technology' started by Phil, Nov 15, 2005.

Tags:
  1. Phil

    Phil
    Expand Collapse
    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
     
  2. Loading...


  3. [MVP] S.Clark

    [MVP] S.Clark
    Expand Collapse
    Guest

    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" <Phil@discussions.microsoft.com> wrote in message
    news:0D5433FB-CB21-4DE7-8620-0F30B2843AB5@microsoft.com...
    > 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
    >
    >
     
  4. Phil

    Phil
    Expand Collapse
    Guest

    Steve Thanks a lot, that worked great

    thanks

    Phil

    "[MVP] S.Clark" wrote:

    > 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" <Phil@discussions.microsoft.com> wrote in message
    > news:0D5433FB-CB21-4DE7-8620-0F30B2843AB5@microsoft.com...
    > > 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
    > >
    > >

    >
    >
    >
     

Share This Page