Combo box list only active records but display inactive selections

[TechMaven]

How would I set up the query for a combo box that has to list only active records (WHERE ActiveSW = True) but still has to display the selection for old records where the choice made is now inactive (ActiveSW = False)? Currently old selections that are now inactive display as blank because the query is excluding them. Thanks.

 

[MajP]

One way to do this is to place a textbox overtop of the combobox (bring the textbox to front). Format it to cover everything but the drop down arrow. Lock the text box. Bind it to the same field as the combo. You are able to get to the choices from the drop down, and the result is displayed in the textbox. It will look like a single combobox.

The only drawback is that you can not type in the textbox. However, I usually have it auto expand the list on the textbox onenter event.

Now if the combo displays one value but stores another then you have to add one more step. Assume the combo displays last names but stores employee ID in the table. Then the text box would have to have something like a dlookup for the control source
=dlookup("LastName","tblEmployees","employeeID = " & empID)
where empID is a bound field of the combo box.