Dropdown List - Need to Retain Inactive Entries

[dkrumholz]

I have a dropdown list control that I use to indicate who accomplished a task on a form that navigates transaction records. The dropdown list is populated from an employee table. That employee table has an active and inactive indicator. When I add a new transaction record I want to be able to select only active employees from the list. But when I navigate old transaction records I want to still show the name of the employee who accomplished the task even if they are no longer active. What is the easiest way to accomplish that?

[June7]

Have code that changes the RowSource. I use only VBA, don't know if macro can do this.

Code in the form Current and/or combobox GotFocus event like:

Me.comboboxname.RowSource = "SELECT EmpID, EmpName FROM Employees " & IIf(IsNull(Me.EmpID),"WHERE Active=True","")
Me.comboboxname.Requery

[dkrumholz]

Do I then need to reset the rowsource to all employee records when it loses focus? I still need to show names that are not active.

[June7]

That's the issue with filtered combobox RowSource that uses lookup when form is in Continuous or Datasheet view. On a new record, changing the RowSource to restrict the list means the RowSource is changed for all instances of the combobox so other rows will not show the alias value because it is no longer available.

The only way I know to handle this is to have an unbound and locked textbox that displays the alias descriptive data and next to it is the combobox sized to just show the dropdown. This means including the lookup table in the form's RecordSource by query join, join type "Include all records from [your primary dataset] ..."

Don't use Datasheet view. Controls in Continuous view can be arranged to appear as datasheet.