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.