Hello all,

In summary i want a form that has employees names listed, and clicking on drop down sets new employee. but there is a problem which i will explain below...

I figured out how to do a form drop down using Design wizard. the drop down itself is filled with the wizard by setting a query in the 'Record Source' property and also by specifying a field in the 'Control Source' which says what field's ID in the row to change if user changes value in the Drop Down.

My trouble is... the tables i have to work with basically looks like this ...
employee Table


ID fname lname
1 John Smith
2 Jake Snake

employee_city Table
ID city
1 Philadelphia
2 NY

Now in the drop down i have this Record Source query where my boss wants the full name of employee...

SELECT employee.Empstatus, [fname] & " " & [lname] AS name
FROM employee

and my Control Source as the ID in the employee_city table.

But when you open up the form, the current field in the form default shows up as the ID number (but i want the concatenated field: [fname] & " " & [lname]). When u hit the drop down, then u see the names since its the record source, which is what i want. But once you click on the name, it doesnt know how to set the Control Source since it doesnt known how to translate employee.name = employee_city.ID i think.

Any ideas? i think a picture might explain it better, if u have email (no file uploads here errr?).