I was working with a friend yesterday and he was showing me how he was showing a bound dropdown list on a combo box on a bound form. He was using a UNION query and VBA to update the combo box RowSource value each time to include another row in the dropdown if the currently selected user is inactive. Normally it only shows active users. This was definitely a clever solution and worked perfectly well.

Here’s the general issue:

1. You have a combo box where you are listing only “Active” users (controlled by an active flag in the users table). The combo box is bound to the UserID field, but displays the UserName field.
2. After you have created a number of records, one of the users leaves the company and is set to “Inactive” which takes them out of the combo box dropdown list.
3. When you look at the combo box on the form for the user that became Inactive, the combo box is now blank. Further investigation will show that the UserID value still exists in the control, but since there is not a matching row, it just doesn’t show anything.
4. The solution is to temporarily add the Inactive User to the list only if their value is in that record.

The way I implement that solution is to using conditions in the query in the combo box RowSource. The SQL for this RowSource would look something like this:

SELECT UserID, UserName FROM Users WHERE
  UserIsActive = True
OR
  UserID = [Forms]![UsersForm]![UsersCombo]

You still need to use VBA for this because the [Forms]![UsersForm]![UsersCombo] component only gets calculated the first time the combo box is filled. So to make sure on each record, the combo box list values are requeried, you need to add a line to the Form_Current event like so:

Private Sub Form_Current()
  Me.UserCombo.Requery
End Sub

This is a pretty simple and elegant solution to this issue in my humble opinion.