Help With Combo Boxes

I'm working on a Access Database to manage different employees in a company. I have 5 different tables but the important tables are Managers, Employees, and Departments. A manager can manage one to many employees and departments, and a department can have one to many employees.

I have a form for employees with Employee Number, Last Name, and First Name as text fields. Department ID and Manager Employee Number which are both foreign keys are currently pull down boxes.

Right now, whenever we get a new employee, we have to individually pull down and assign an employee to a Manager and Department via 2 seperate combo boxes.

The question I have is how can I get Access to do this in one step, so that once I pull down and select the appropriate Department combo box Access will in turn select the correct corresponding Manager who manages that Department?

Thanks in advance.

BTW, I have atached a bmp of the ERD Diagram for this Database as well as a picture of what the current Employee Form looks like.

I think that the link between Employees and Managers should be removed.
If you need to know the manager of an employee, you'll get it thru the Departement table.

By the way, welcome to this forum!
Newman

ERD Diagram

Here's the ERD Diagram which didn't make it here the first time around.

This link is neccessary since a managers first and last names are fed to the employee form from the managers table.

Sorry, but I don't understand why you put the manager's name in the Employee's table. If you put the department in that table and that there is only one manager per department, you don't need the field Manager's name in the form, nor the table.
If somewhere else you need to know the manager for an employee, you use a query with these tables: TEmployees, TDepartments and TManagers. Taking the employee's name from TEmployees and the manager's name from TManagers. The Department table just serves you as link between the others.
Newman

I agree that your relationships need a second glance, but here is how you might solve your original combo box problem.

After you update the cboManager, have the cboDepartment lookup its value from the table.

Like This

Sub AfterUpdate_cboManagerLastName

Dim managerID As Integer

'Get the ID of your Manager
managerID = Dlookup("[MgrEmployeeNumber]", & _
"Manager", "[LastName] = '" & Me.cboManagerLastName & "'")

'Use the Manager's ID to set the New Employee's Department
Me.cboDepartment = Dlookup("[DepartmentName]", & _
"Department", "[MgrEmployeeNumber] = " & managerID)

End Sub


This should work, but I would really recomment putting the Department Table On Top of the Manager table.

This way it goes Department -> Managers -> Employees -> Employee Transactions.

This will save you a lot of head aches in the future.


Good Luck..