After Update SendEMail

[gdgonzal]

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.

 

[gdgonzal]

Thanks, I should mention this is an Access Web Database, all I can use are macros...code isn't supported on a web database.

 

[elbowman]

It sounds like you and I are having a similar problem. I'm using the Microsoft Issues web-enabled template to implement a database for my school district. I have bus drivers who need to have an assigned person at each school review a report when a student misbehaves on the bus.

I have modified the Users table to include one new field, called School, and included it on the User Detail form.

On the IssueNew form I've added several fields, including School. I use a combo-box for the School field on the Issues form. Once the school is selected I've created another combo-box field that gets the value of the school's assigned issue reviewer's email address. I know that my Row Source SELECT code works properly because I can see the correct reviewer's email address appear in the field. Also, on the IssueNew form I've added a button to send an email to the reviewer. It simply saves the record then calls another macro I've placed in the Issues table. (I've attached a screenshot of the table macro for you to look at.)

I'm getting an error when the Send Email button is clicked that, 'The SendEmail action failed because no recipients were specified.' Also, when I look at the Issues table, fields that I populated on the Issues form are not getting populated including the AssignedTo reviewers email address. So, I think this is the crux of the problem. Somehow the SELECT statement is not actually populating the data.

How are you doing with your problem? Have you gotten any ideas or solutions?

Lance Bowman