Combo Box to display only "active" records

[Nancy]

Hi,

I have a combo box based on a list of records in table. Overtime I may want
to "de-activate" (Employee.Active=No) records in the list so that a user can
longer chose it from the list, but I don't want previously selected records
from list to be affected.

I added a WHERE Employee.Active=Yes condition to the row source. My
drop-down list no longer displays inactive records, but when I look at
previous entries the record displayed is also blank.

How do I only get this to apply to new records being created?

Thanks

 

[Allen Browne]

Yes, that's a common scenario, Nancy.

One workaround is to sort the inactive records to the bottom of the list.
That way it can still display the old records, but you choose the active
ones first when typing in the combo.

The RowSource of the combo would be something like this:
SELECT EmployeeID,
Surname & ", " + FirstName AS FullName
FROM tblEmployee
ORDER BY Active, Surname, FirstName;