Combo Box Display

[RedHeadedMonster]

I have a combo list where you select a Responsible POC. Want to limit the selection of list to current POCs. But want to be able to display all POCs for historical records even tho they are no longer current.

Table of POCs is set up to hold pocID (#), pocName (text) and NLPOC (checkbox). Ofcourse the combo box is set up to display the pocName and store the pocID. I had it set up to not show any POC with NLPOC checked as true. Which is fine for new records. But in an editable view of data, if a previous POC was selected but now is NLPOC they dont display in Combo box so it appears as if the field is empty.

Is there anyway to have combo Box allow selection of only current POC but still have ability to display previous POCs for historical purposes?

Thanx!
RHM

 

[missinglinq]

I can think of two basic approaches:

First method would be to have two Queries/SQL Statements, one with all POCs and one with current POCs to use as RowSources for the Combobox. When Focus moves to the Combobox, use code like

Private Sub ComboboxName_GotFocus()
 If Me.NewRecord Then
  'Code here for RowSource with Current POCs only
 Else
  'Code here for RowSource with All POCs
 End If
End Sub

Second approach would be to have all POCs in the RowSource, then in the BeforeUpdate event of the Combobox, if a selection is made of a POC who is no longer current, Cancel the update and pop a Messagebox, telling the user that the POC is not available. The Focus will remain on the Combobox and the user can then make a valid selection.

Personally, I always avoid this by not actually having a Combobox that is Bound to a Field, but rather to have an Unbound Combobox, then assign the Value that is selected to a Textbox that is Bound.

But this is a personal choice; you mileage may vary!

Linq ;0)>