Update Text Box based on List Box Selection on Access

[wwce]

My data set are:

- Form titled "Form Members"
- List Box named "Agents"
- Text Box named "EmployeeID".
- Table Source called "tblAssociate" where the employee is under "AssociateName" and employee ID under "AssociateNumber".

When the user clicks on an agent's name in the List Box, the Text Box is supposed to populate agent's employee ID.

QUESTION: How do I get that to work?

So far... my thinking is on the "On Click" event procedure on the List Box is to add a DLOOKUP code, but I can't get it to work:

EmployeeID = DLookup("AssociateNumber", "tblAssociate", "AssociateName = " & [Forms]![Form Members]![Agents])

However, it doesn't work.

Any help is greatly appreciated, thanks.

 

[TerryHogarth21]

My data set are:

- Form titled "Form Members"
- List Box named "Agents"
- Text Box named "EmployeeID".
- Table Source called "tblAssociate" where the employee is under "AssociateName" and employee ID under "AssociateNumber".

When the user clicks on an agent's name in the List Box, the Text Box is supposed to populate agent's employee ID.

QUESTION: How do I get that to work?

So far... my thinking is on the "On Click" event procedure on the List Box is to add a DLOOKUP code, but I can't get it to work:

EmployeeID = DLookup("AssociateNumber", "tblAssociate", "AssociateName = " & [Forms]![Form Members]![Agents])

However, it doesn't work.

Any help is greatly appreciated, thanks.

In your list box property - the after update event is where you could put your dlookup.

 

[wwce]

In your list box property - the after update event is where you could put your dlookup.

Thanks, I tried that, still getting the same error

Let's say I click the name 'Jane Doe' in the List Box, the error I get says:

Run-time Error '3075'
Syntax error (missing operator) in query expression 'AssociateName = Jane Doe'

 

[TerryHogarth21]

try this

employeeid = dlookup("AssociateNumber","tblAssociate","AssociateName= '" & Me.Agents & "'")

 

[wwce]

try this

employeeid = dlookup("AssociateNumber","tblAssociate","AssociateName= '" & Me.Agents & "'")

Worked!

Thank you!

 

[TerryHogarth21]

Text syntax requires those quotations or double quotations, what you had before it was looking as a number in essence and couldn't match the employee name (since the AssociateName field is likely text).