Combo Box control - preserve old data; prevent new selections

[MeisoT]

I have a combo box control on a from that allows users to select from a list of managers. Once the manager is no longer active, I don't want the manager's name to be available for selection from the combo box. However, If you return to a previous record containing data when the manager was active, the text portion of the combo box must still display the, now inactive, manager's name.

Is there a way to accomplish this? How do you remove a name from the list on the combo box so that it can no longer be selected, but still display the name in previous records?

Access version = 2000

Thanks.

 

[Remou]

In a reply to your other post I said,
I guess the easiest would be an additional field that flagged whether or not to display the row. You could then set the combo box to select where display is true.
To add to that, if your select also includes the code from the current record and a requery On Current, you should be able to display ex-managers. The SQL for the combo would look something like:
[tt]SELECT CODE, Manager, DisplayYN
FROM tblManagers
WHERE (((tblManagers.Display)=True)) OR (((tblManagers.CODE)=[forms]![frmManagers]![cboManager]));[/tt]

Then:

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

 

[MeisoT]

Thanks Remou for your response to both of my posts to the dreaded combo box problems! Your code above has given me something to think about in working around these problems. I will give it a try!