After Update SendEMail

Hello Experts:

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.
ShadowITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
It would help to have more detail about the form design.

For this to work you have to someway read the information from the user table.  There are several ways to handle this. If you have the userID, you could use the DLookup() function to retrieve the email address.   If ther is a combo box on the for for selcting user then yu coudl add the email as a hidden column and reference it as needed.
0
ShadowITAuthor Commented:
Thanks for your reply, actually this wouldn't be dependant on any form.  Although I do have a form that enters records into the main tbl; instead this would be based on the record state within the main tbl being updated.  

I can accomplish this by hardcoding an e-mail address with SendEmail macro as an AfterUpdate event, but I am looking to pulling this dymanically from my User tbl.

I can't do a DLookup() as this is a web database and I'm only able to use macros, no code allowed.  I was able to find a Lookup macro, perhaps this would work?  Unfortunately I am not sure of the syntax I should be using in the Where section.  Then I'd also need to know how to relate this back to the SendEMail macro.
0
Helen FeddemaCommented:
Try saving the Manager Email to a String variable, and using that in your code.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Ah ... I missed that this was a web database. This new site skin is really hard to see. Still trying to get used to it.

I have not had any need for the web database feature of 2010.  

I did a little research and these may help:

http://www.access-programmers.co.uk/forums/showthread.php?t=196662

http://www.utteraccess.com/forum/Tempvar-Change-T-t1962140.html

FWIW: The AccessJunkie that has replied in the above links works on the Access Product Group at Microsoft.
0
Jeffrey CoachmanMIS LiasonCommented:
This is the downside of using "web" databases...
You only have macros.
You can't automate these database in the same way as you can a standard Access db.

You may want to look into a product like this:
http://eqldata.com/index/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShadowITAuthor Commented:
Perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.