Combo Box I want to show Old Values but not when updating

[time2hike]

In my database I have a form that allows the user to view and update data. I use combo boxes to standardize entry of the data. The issue that I have found with combo boxes is that when the data goes out of date, i.e. the Employee leaves, the user can no longer see the employee on the form. This has to do with the list values being limited to Active Employees.

What I want to do is show all of the employees on the form but only allow the user to select the active employees when they are updating the record.

How do you get around this issue?

[Seth Schrock]

You could use the control's BeforeUpdate event to check if the employee is active. If they are not, then cancel the event, undo the change and show a message. Here is an example from the MSDN website: ComboBox.BeforeUpdate Event.

To help the users know which ones are active, you could add another column to your combobox that displays this information. It would only show when the box is expanded, but it would help.

[time2hike]

Thanks Seth, I solved this by adding a text box that contains the Employee's name from the lookup table as a part of my base query, then making the combo box shrink to show only the drop down arrow part of the box and limited the combo box list to active employees. By putting combo box next to the text box the user is able to see them as if they were one field. When they open the Combo box list they are able to select an active employee and the text box updates automatically.

It feels messy to me but it does what I needed. I am open to feedback on this method of solving the problem of seeing the employee currently selected, who may no longer be an employee, and allowing selection of only active employees.

[jforbes]

I think this behavior only happens when using a key column and an additional display (human readable) column, and maybe Limit to List turned on. If you were to save the Employees name into the Record on the Form, it would display inactive Employees, but only allow you to select active Employees.

That would probably be a pretty serious change for you, so you might want to include the currently selected Employee in the RowSource for the ComboBox. It's not as clean, but it would be prettier than what you have. To do this, include something like this as the RowSource of the ComboBox:

SELECT Employee.ID, Employee.FullName, Employee.Active
FROM Employee
WHERE Employee.Active=True 
OR Employee.ID=[Forms]![TheCurrentForm]![ComboBoxName]

Then you'll need to add a Requery in the OnCurrent Event of the Form:

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

[NeoPa]

I have a very similar situation. Seth's approach works fine for me.

[jforbes]

Oh, yes. I didn't mean to say that Seth's approach wouldn't work. It's a great approach, especially if you want to allow your users to select inactive employees and warn that they are doing so.

I just wanted to offer it up as an option, because there are times when only an active employee should be selected. For me, limiting the list to only active employees is preferable to listening to users ask me, "If we are not supposed to select inactive employees, then why are they in the list?"

[NeoPa]

JForbes:
"If we are not supposed to select inactive employees, then why are they in the list?"

My answer to that one is the simple truth - Because the list covers existing records as well as valid selections for new or updated ones.

Having said that, your suggestion is perfectly valid. It's a tidy solution if twittering users are a problem (and they can be). There's always room for more solutions.