Populate field with drop down list

[Patchy]

Is there a function that will populate a field with drop down menu based on two criteria?

I want the the fields with first and last name to populate with drop down lists based on the employee code I have inputted in the form and job title from a query.

Path: looks at employee code from form > looks at specific job title from query > pulls out all first names in one field and all last names in another field with the same job title in drop down list from query

Employee Code: 100

Returns all employees' first names in first field with same job title:

Prince
Tina
Greg

Returns all employees' last names in second field of form with same job title:

Fey
William
Jones


Here's what the query looks like in datasheet view:

Location #       First Name             Last Name             Job Title                 Employee Code
1                     John                  Smith                 Technician                     100
2                     Jane                  Doe                    Manager                     100
2                     Greg                  Jones                 Engineer                     100
1                     Prince                  William                 Engineer                     100
1                     Tina                 Fey                 Engineer                     100

I've been trying to get dlookup to work, but no luck. Here's one of my formula:

  =DLookUp("[Last Name]", "[Employees tb]", _  "[Employee Code] = Form![Employee Code]" & "[Employees tb]", _ " [Manager]")

Edit: Here are the new formulas I'm playing with:

SELECT EmployeeCodeONLY.[Employee Code] FROM EmployeeCodeOnly;

SELECT [Employees tb].[First Name] FROM [Employees tb] WHERE ((([Employees tb].[First NAME])=[Forms]![Form1]![Employee Code]));

The first is linked to a separate table that only contains employee codes because query I am working with has duplicates due to multiple records.

The second is trying to link both the table and query together to populate only first name.

I don't know how to include the second criteria, job title, to refine it more.

 

[MLUCKHAM]

So, the first query needs to use Group By to limit the duplicate employee codes.


SELECT [Employee Code] FROM [Employees tb] Group By [Employee Code] Order By [Employee Code]

The second query is straight forward:-

SELECT [First Name] FROM [Employees tb] WHERE [Employee Code]=[Forms]![Form1]![Employee Code] and [Job Title] =Forms![Form1]![Job Title]

You need to add an event to the onChange event of the drop down lists to requery the first and last name drop down lists.

However, you really need to add a primary key to your table to uniquely identify each record. You can then display the full name in one drop down list by sung the number of columns. You can select three fields, the id, first name and surname. Set your column count to 3 and your clan widths to 0, 3,3. By using zero the id field would not be shown, but you can still use it to uniquely identify a person. Have a think about this technique...!