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.

I'm having a bit of a hard time understanding what you're trying to accomplish...

You have a combobox that contains multiple fields (UserName, UserEmail, Manager, ManagerEmail) and when one of these values is changed, you want to notify the manager of the record that there has been a change?

In order to reference a specific column in a combobox you can use a command like cboYOURCOMBOBOX.Column(n) - where n is the column in the combo box you're referring to. So if the Manager Email is in the 4th column, cboYOURCOMBOBOX.Column(3) as the column counting starts at 0.
You could also do a DLookup to determine the ManagerEmail based on the User that has been selected. DLookup("ManagerEmail", "somekindofquery", "UserID = " & Me.cboYOURCOMBOBOX & ""). somekindofquery could be a predetermined string in VBA or it could be a query you've built and saved within Access.

Not sure if that helps... but I hope it might.

Thanks for your replay, actually the combo box on the form is only displaying the user name by using column count and width. Within the actual Main tbl it shows everything within the User tbl; the only reason I decided to include the other columns was to somehow get to the manager's e-mail address within the User tbl.

I suppose my real question is how do I access the manager's e-mail within the User tbl using a macro? Would Lookup work? If so how?

Even if the columns aren't actually visible in the cbo, they're there - which means you can still reference to the Manager Email if it's in the cbo rowsource query. So if you have a combobox with an ID, UserName, ManagerName, ManagerEmail and your Column count is 4 with the column widths set to 0;1;0;0 you will reference the ManagerEmail address with cboYOURCOMBO.Column(3).

That being said, if you want to do it via a Dlookup, create a query and call it something like qryUsersAndManagers and do as I said in the last post. strMgrEmail = DLookup("ManagerEmail", "qryUsersAndManagers", "UserID = " Me.cboYOURCOMBO" & "")
So long as you have the UserID in the query and the UserID is what is running the combobox.

Edit: Spelling.

This post has been edited by Squire4Hire: Apr 13 2012, 12:09 PM

Doesn't look like SendEMail likes the syntax for moving through columns.

Hmmm... Try using an explicit form of the command (Forms!frmYOURFORM.Form!cboYOURCBO.Column(4)

If that doesn't work, another way would be to put the ManagerEmail into a textbox using that formula and referencing the text box. You could then make the txtbox hidden should you not want to show it on the form.

ie.

txtManagerEmail = Me.cboYOURCBO.Column(4)

SendTo = txtManagerEmail

The explicit form didn't work either, but I like your idea of displaying the ManagerEMail within the form that populates the Main tbl and then making it not visible. I suppose I would need to add a Manager E-Mail column to my Main tbl, easy enough. I added an unbound text box to my form and the tried the set the defaiult value using = Forms![ProductivityDetails]![Buyer]!Column(4) but I got an error saying that this expression is not compatible with web forms. (IMG:style_emoticons/default/pullhair.gif)

I didn't realize you were using web forms. Hmmm...

Also, there's an error in this = Forms![ProductivityDetails]![Buyer]!Column(4)
It should read = Forms![ProductivityDetails].Form!Buyer.Column(4) (so long as Buyer is the name of the combo box control)

Not sure if that will make a difference but give it a try.

Webforms are another issue all together. Give me a few minutes and I'll see if I can't hash something out...

It seems to work for me just fine. I'm thinking that your syntax is just wrong.

I was so hoping this would work; but no such luck. I went and checked it each time and as soon as I entered ".Column(4)" I started getting an error. Thanks for the help!

And what about setting the command in a textbox on the form with the right syntax? Sorry... that was what I tested - not the first issue.

I was able to place a textbox on a form and have it =Forms!myForm.Form!cboMycbo.column(4) then in the macro, reference the SendEmail command to =txtMyText.

I'm sure this should work for you.

Can you provide an example of this so I can see exactly how you're doing this? Thanks again

First of all, where are you running this command from?

You seem to be suggesting that you're running this in a table trigger, and if you look closely at a table trigger, it has no idea about global variables, and has absolutely no way of referencing some form that might be running on your iPad.

You have this setup:
(IMG:https://public.bay.livefilestore.com/y1pestRptXWCXbGjES4q4S3KXVH5VazlwnGuyAzQo-FMB7y9GqlL8W7sGtFn8VuUwzfM0edO0fJXXqBYF8K6v0S-w/HOW%20WEB.png)

The table triggers are running half way across the world on the server (on the right side).

Now at the end of the day, understanding and knowing about the above setup is not required, but if look close at those table triggers, they ONLY have local vars, and they know nothing about code that runs in your web browser (on the left side in above).

And use intei-sense here to help you. In that table code you see no forms, and no controls appear while you enter code. And as noted, you cannot reference web forms in that code which all of a sudden might be browsing UtterAccess or now showing Ebay!

It would be really cool if a web server could reach out and talk to and grab data from any web page. Since then we would write something that scans the internet for anyone doing their banking!

So table code never talks to a web form. A web form can however call table code and cause table code to run.

So, server side or table code cannot "reach out" to a web form.

So in your table code, you can't reference of form, because there's really no way that knowing even if that web form is at all even opened.

So the first suggestion is here to rely on inteli-sense in the editor here. If intel-sense is not working, then the code you writing will not work.

Because web is new to people and you might not yet have committed the above diagram to memory, then just rely on the code editor to TELL YOU what is possible and what is not until you over time find this to become second nature without thinking.

The challenge here is that writing code in different places has vasthly different features and the reason is above (code running in a browser is very different from code running on the server in a table. So table code = server side code, and form code = web browser side code.
As noted in web forms you most certainly can reference controls, but the column() command is not available and there are few simple tricks and work arounds that I use, but in this case we are NOT running code in a form, but in a table, and thus no forms controls are required here anyway.

I should point out it is easy to place a email button on a form,, but you as noted are doing this at the table level.

Ok, assuming we're using the after update event for this table, then the logic is going to be as follows:

Grab (look up) the correct email name based on the current record and that of the user ID
.
Executer a send mail command based on the email name we fetched.

Therefore, you code in the after update event will look like:

(IMG:https://public.bay.livefilestore.com/y1pu59clishJheuQk-aosb3NSAB3G-ur-fEzOJSmpp2DrMxuh54sUWCjHKufbZVnwNTUEgSkIj7bT_3fiHBhYyDKA/sendmail.png)

One more tip: Pay CLOSE attention in the editor if there is a "=" sign already showing in the code editor. If yes, then what you type is an expression as opposed to a literal.

And in the above, note how for the to: I typed in a = sign, and again if you do this, then inteli-sense will show for you since with the = you are now building an expression. In other areas when writing code, if you see the = already in place, then you do NOT need to type the = to start a expression.

For example, in above, the where expression is ALWAYS a expression, and the editor "already" as a = as shown above.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Perfect! You're the man Albert and thanks as well for the comprehensive explaination! (IMG:style_emoticons/default/notworthy.gif)

Thank you kindly. I can say that the hard part is the "change".

The problem is we do this stuff in our sleep with client database, and that what makes this SO VERY frustrating since we use to this being oh so very easy.

I think I going to do a public web seminar and invite everyone here. I will go through a top 10 list of things to learn and avoid. I going to announce this 1 hour web seminar in about a week and will invite everyone here on UA to attend (I will use live meeting or lync).

It is amazing how frustrating this can be, but it also amazing at the results "once" you get a hold on a just a few tips and things…

Keep at this, it WILL become second nature in a short time.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Thanks for jumping in there Al. An excellent explanation.

Edit: I'm definitely keen on that webinar as well... I'll be on the lookout for it. Cheers!

This post has been edited by Squire4Hire: Apr 17 2012, 10:05 AM

While I am not using web service this was still very helpful, got mine to work.

Thanks!