Help with Look-up Query on a Combo Box, Please

[GaryL]

Hi

I hope this is in the right section, only just stumbled across this site, and am not to sure of my way around it yet. So please let me know.

Ok, so basically I am creating a very basic database to store employee training details.

The above is the basic layout of tables.
The field tblEmployee.DepartpmentName is a combo box lookup field where it is linked to a Query that lists tblTraining.DepartmentName, so only departments that are set up are listed, they are also grouped so no multiple data.

Now I want to create a similar but more complex lookup for the tblEmployeeTraining.TrainingID field. So when I input an Employee ID into tblEmployeeTraining.EmployeeID and then go to select tblEmployeeTraining.TrainingID from a drop down combo box, only those Training ID’s whose department name equals that of tblEmployeeTraining.EmployeeID departments name are listed.

Now I have made a stab at this, this is the SQL code behind the query:
SELECT [tblTraining].[ID], [tblTraining].[TrainingName]
FROM tblTraining INNER JOIN tblEmployee ON [tblTraining].[DepartmentName]=[tblEmployee].[DepartmentName]
WHERE [tblEmployeeTraining].[EmployeeID]=[tblEmployee].[ID];

This almost works, I have my combo box for tblEmployeeTraining.TrainingID but when I click on it I get the following input box. It doesn’t seem to see my value in the Employee ID field.

I can enter an employee number here and the correct Training ID’s based on the employees department name are listed. How can I get this to read the EmployeeID automatically so I don’t have to input it again?

Many Thanks
Gary

 

[GaryL]

Here is the design few of the same query if it is of any help.

 

[GaryL]

can anyone help

 

[JustinDB]

not really getting what you want ifyou want to make a table with the trainer ID's and on a other place you want to select this trainer instead of retiping it again you should use the search wizard in table dezign wizard. and than choose search record from table/query instead of the records are beiig typed and that chould do it.

that is if this what what you meant with your question

 

[neileg]

I know these table level lookups are a feature of Access, but really they are more trouble than they are worth. You should be entering your data throught a form, not direct into the table, and in a form you have much more control over the combos than in the table.