Need help with form/subform

[mitchem1]

I have 3 tables in an old database I am trying to convert to Access. 1)Employees, 2)Equipment, 3)Assignments (stores what equipment has been assigned to which employees). I would like to have an Employee combo box at the top of the form. When the user selects an employee, all equipment assigned to that employee should be listed below. I also need an Equipment combo box. When a specific item is selected, the list will display the Employee in possesion of that item. Can anyone point me in the right direction to accomplish this task. Thanks in advance.

 

[chrismcbride]

You will have the Employee table related to the Assignments table (1:n) and the Equipment table related to the Assignment table (1:n). Make a form (Employee) and Sub Form (Assignment).
The combo box full of employees is an unbound combo box with a SQL string that selects the employees from the Employee table and is navigational. You may also want to have a bound control that holds the current employee #.
If the table are related, then the subform will show assignments that have the matching employee number. Each assigment will have a equipment number and a bound combo box that is populated by a sql string that selects all the equipment from the equipment table can display the names.
The last bit I have a bit of logical trouple with. You could have the combo box display the currently related employee from the assignment table. You could even display the name if you structure the SQL correctly. However I am not sure how you want to handle the peices of equipment. Once a piece of equipment is assigned, how do you ensure that it is not re-assigned to another employee and/or how do you un-assign or return a piece of equipment?
Chris