Results 1 to 4 of 4

Thread: AfterUpdate SendEMail Macro

  1. #1
    gdgonzal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3

    AfterUpdate SendEMail Macro

    Hello All:

    I have a Main tbl where I am storing a lookup leading to a User tbl. Within the User tbl I am maintaining:

    User Name, User E-Mail, Manager & Manager E-Mail

    I have set the Main tbl to display all of this information within its lookup, when a record is updated I was hoping to use this to notify the corresponding manager.

    In other words, if Bob is the user then Bob's manager Joe would be notified because this relationship is maintained within the User table when a record is updated.

    I have tried doing this various ways within the To field of SendEmail but no matter what I try it doesn't work. I typically get an error stating that "User" isn't defined. So =([Users].[ManagerEMail]) doesn't work and I cannot seem to define a path to [ManagerEMail] from the [Users] defined within my Main tbl which I suspect to be my issue. Not sure if there's a syntax to move you through specific columns within a lookup.

    I suppose I could just add the manager's e-mail directly to the form that populates the tbl but I'd like to see if there's a way to leverage the existing relationship that already exists within my User tbl.

    Any ideas would be much appreciated, thanks.

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,196
    What is the RecordSource of the form?

    The easiest way to take advantage of the 'relationship' might be to include the Manager and ManagerEMail fields in the form's RecordSource. Even if the fields are not in textboxes, can refer to RecordSource fields of the current record.

    If those fields are not included with the form RecordSource, use DLookup function.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    gdgonzal is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3
    The record source for my form is the Main tbl. I can't use DLookup because it's not availble within the expression builder of SendEMail because this is a Web Database; my apologizes for not being more explicit about this.

    The real issue here is that the only way to get things done in a web database is to use macros. I see a Look Up macro that seems promising but I have no idea how to defined the Where portion. (see pic)

    I have also tried using explicit columns since my user name in my Main tbl eminates for my user tbl I thought I could just scroll to their manager's e-mail in the To field of SendEMail but I received an error saying it wasn't compatible with Web Databases.

    Click image for larger version. 

Name:	Look Up Macro.jpg 
Views:	14 
Size:	168.4 KB 
ID:	7163

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,196
    If DLookup (or any aggregate functions) not available, I expect WHERE will have to refer to field of form RecordSource or a control on the form. Still don't understand what 'Main' table data is. Try including the User table in the form's RecordSource.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. AfterUpdate not firing
    By newvb in forum Forms
    Replies: 4
    Last Post: 09-29-2011, 03:55 AM
  2. AfterUpdate event help
    By 10 Gauge in forum Forms
    Replies: 11
    Last Post: 09-08-2011, 09:04 AM
  3. Understanding afterupdate
    By MAM8433 in forum Access
    Replies: 8
    Last Post: 05-23-2011, 01:05 PM
  4. AfterUpdate not updating
    By P5C768 in forum Programming
    Replies: 6
    Last Post: 06-10-2010, 01:31 PM
  5. BeforeUpdate and AfterUpdate Question
    By gsurfdude in forum Programming
    Replies: 0
    Last Post: 04-03-2008, 06:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums