Dlookup a value From another table.

[Locopete99]

Hi Guys,

Can someone check my code for me and point out where I'm going wrong?

I have a table called Tbl_Salesman. It has 4 fields

Salesman
RSM
Salesmans E-mail
RSM E-mail.

I have a combo box on my form that allows me to link these in so that the user selects the Salesman and an e-mail button picks up the e-mail for the salesmand and RSM.

I have another form that is linked to the main form, which I also intend to put an e-mail button on. I have the salesman field on the form, but hidden.

I'm trying to use the below code to find the respective e-mails. Can someone tell me where its going wrong?

Dim Salesman As Variant
Dim RSM As Variant


Salesman = DLookup("Salesman E-mail", "Tbl_Salesman", Me.[Salesman])
RSM = DLookup("RSM E - mail", "Tbl_Salesman", Me.[Salesman])

At the moment I'm getting a syntax error on "Salesman E-mail" and i fear I'll have a few more once thats sorted!

 

[Minty]

Assuming Salesman is a text field you need to specify what field your are testing in the criteria part and surround the expression in single quotes;

Salesman = DLookup("Salesman E-mail", "Tbl_Salesman", "[Salesman] = [COLOR="Red"]'[/COLOR]" & Me.[Salesman] & "[COLOR="red"]'[/COLOR]")

The same would apply to your second Dlookup.

However , I would be tempted to add an EmployeeID as a Autonumber to your table. Then instead of storing a name in your RSM field you would simply store their EmployeeID and you wouldn't need to store their email as you could look it up from the RSM ID....
Also if the RSM changes you only change that RSM ID field, you would automatically be looking up the correct email address after changing just that one field.

edit - Thank you CJ - I can't type today...

 

[CJ_London]

typo's - still missed one

"[Salesman] = '" & Me.[Salesman] & "'"

 

[Locopete99]

Thanks guys!!