display filtered combo box value

Question

On many forms throughout our database, a combo box with a list of company personnel is displayed. In the TblPersonnel, I've recently added a 'Current' Y/N field to filter out employees that are no longer employed here. 

Now, when a user goes to select a name from the list in the combo box, it will only display current employees. But on the older records, where a former employee entered his/her name in the combo box, I will still like those values to display. Is that possible?

The only way I can think to do it would be to make a 'fake' combo box which actually is a really short combo box next to a text box. The combo box filters to current employees, but the text box is not filtered & thus shows all values. But this seems cumbersome to do in 50+ different locations. Any other ideas?

Thanks in advance.

Answer 1

If you change the RowSource of the Combobox to filter for only Current employees and you have combos on forms that will display non current employees, you will have an issue.

The way I handle this is in the Got Focus event of the combo. By default the RowSource is unfiltered. So when a user views a record, they see the employee assigned to that record. But in the Got Focus event I change the RowSource to a filtered SQL statement, so the user can only select from current employees.

Me.cboEmp.RowSource = " SELECT EmployeeID, Lastname & ", " & Firstname AS Fullname FROM tblEmployees WHERE Current = True ORDER BY Lastname, Firstname;