Problem with form to find and populate records
Hello, I'm trying to develop a staff/employee database and have run into a problem:
I have 3 main tables
tblEmployee (table of all employee basic details)
tblPost (table of all posts in the department)
tblEmployeeInPost (main table of employees and their assigned posts)
The prob is I need a form to populate the tblEmployeeInPost table - I want the user to be able to select the employee from combo box (showing various details from tblEmployee). I then want a subform(possibly) to display empty records in the tblEmployeeInPost table so that user can add entries in this (to keep a history of Employee posts and changes for example) - see attached screenshot - this is pretty much how I want it to look.
However this does not work, when I select a new employee from drop down the subform still shows same details and I can't seem to assign this to each employee. Does this make sense? The tblEmployeeInPost table has a link to tblEmployee on EmployeeID (one tblEmployee to many tblEmployeeInPost)?
Can anyone help with this?