Related fields populating in Datasheet view

[kstrecker1701]

I've designed a simple form for entering new employee information. The main table it's based on is EmployeeInfoT. The primary key is EmployeeID, and the other fields store basic info like FirstName, LastName, HireDate, etc.

I have another table, DepartmentInfoT, that stores Department and Manager info. Each Department has only one Manager.

I've joined the DepartmentInfoT table's primary key (DeptID) to the Department field in the EmployeeInfoT table. Each Department can have several Employees.

When I input a new employee's info in my form, I can choose the Department from a dropdown box, and then the corresponding Manager's name automatically appears in a text box field below it. This is perfect - exactly what I want to happen.

However, if I input the info directly into the EmployeeInfoT datasheet, I can't get that Manager field to populate correctly. It gives me another dropdown box with all the Managers' names to choose from. So it's possible I could choose the wrong Manager for the given Department.

Is there a way for the correct Manager's name to automatically appear in the Datasheet view, just like it does in the form? What other info do you need in order to assess this situation?

Thanks!

[ranman256]

the DEPT field should be in tEmployeeInfo
to get the manager, use a query to join tEmployeeInfo.Dept to tDeptInfo.Dept and add tDeptInfo.Manager field.

[kstrecker1701]

add tDeptInfo.Manager field.

To what? The query or the EmployeeInfoT table?

If I create the query joining those two tables (EmployeeDeptMgrQ), then if I try to create a Manager field in my EmployeeInfoT table that draws data from the DepartmentInfoT_Manager field in the query, I get the error:

"No valid fields can be found in 'EmployeeDeptMgrQ'. You may have selected a query that uses the table you're adding the lookup column to. Please select a new source."

I must be missing some basic piece of info about how tables and queries work.