Solved

After Update SendEMail

Posted on 2012-04-13
6
339 Views
Last Modified: 2012-05-16
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.
0
Comment
Question by:ShadowIT
6 Comments
 
LVL 21
ID: 37842896
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
 

Author Comment

by:ShadowIT
ID: 37842985
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37843851
Try saving the Manager Email to a String variable, and using that in your code.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 21
ID: 37843982
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37854214
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
 

Author Closing Comment

by:ShadowIT
ID: 37977319
Perfect!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now