Combo box that displays current values but form must keep old values

[GT_engineer]

I want a combo box to display only current employees, so I have a combo box that displays employees name, with a query
WHERE (Status = TRUE)
meaning that employee is currently working for the company.

Problem with this is, when that employee leaves (Status = FALSE) then all forms that use to have his/her name now show blank.

Is there a way for the combo box to display only current employees but the form retains all of its past information???

 

[AccessMSSQL]

In your form recordsource do a join to the tblemployees table and then on your main form display the employee name. Make your combo box un-bound and in the after update event, set the employeeID in the main table to the combo box selected.

 

[GT_engineer]

Thanks AccessMSSQL
Will I have essentially have two boxes, the selection combo box and the display employee name box?

And I'm sorry but I don't quite understand your first statement
"In your form recordsource do a join to the tblemployees table and then on your main form display the employee name."

Can you please re-explain. Thanks

 

[AccessMSSQL]

Yes, you should have a query or table bound to your form. You can see this by going to design view of your form and bringing up the form properties box. Go to the recordsource property. It should have your query or table name. Click on the ".." next to the recordsource property row and then modify the query. Add the employees table to that query and join it properly to your main table. Then add the employee name to the query grid. Save this back to your form. You will only have one combo box. the employee name will just be a textbox that is un-editable.

 

[GT_engineer]

Thanks AccessMSSQL, that will work