Access 2000 Combo box oldvalue property

I am using Access 2000, have a table with 2columns, Column 1 stores 26 Names and column 2 stores a boolean value(used to check whether or not that name is currently being displayed in 1 of 8 combo boxes).

TableColumns:
ColumnName(Text) &
NameSelected(Y开发者_StackOverflowes/No)

When a combo box value is selected I want that value to be removed from the list of the other combo boxes, so it eliminates duplicates. I have a query that populates the combobox and it selects all names where NameSelected is false.

Problem:

I have tried to access combobox.oldvalue from a number of different events, yet it always seems to hold the new value.

Am I doing something wrong? If so, any ideas of what?

Or is there a better way to do this? I have tried not having the 2nd column and just having queries for each combobox that selects all the names from the table where not equal to the selected text of the other controls, but this never displays anything.

OldValue property

OldValue only work on bound fields. The documentation says:

The OldValue property contains the unedited data from a bound control and is read-only in all views.

If you need to keep track of the old value for an unbound control, you can do it manually from code: for instance you can use the form's Current or Load event or the combo box's BeforeUpdate event to load your initial values into variables defined in the VBA module of your form.

A (potentially) better approach

You should not need to maintain a dedicated boolean column to find out if the field was displayed in another combo or not.

Let's use a concrete example:

Assuming that you have 3 combo boxes on your Form1: Combo1, Combo2, Combo3.

I want to display a list of countries in each, and exclude from the list those countries I already selected in the other combo-boxes.

Set the RowSource of Combo1 to:

  SELECT Country.ID, 
         Country.CountryName 
  FROM Country 
  WHERE (Country.ID Not In ([Forms]![Form1]![Combo2], 
                            [Forms]![Form1]![Combo3])) 
  ORDER BY Country.[CountryName]; 

Set the RowSource of Combo2 to:

  SELECT Country.ID, 
         Country.CountryName 
  FROM Country 
  WHERE (Country.ID Not In ([Forms]![Form1]![Combo1], 
                            [Forms]![Form1]![Combo3])) 
  ORDER BY Country.[CountryName]; 

Set the RowSource of Combo3 to:

  SELECT Country.ID, 
         Country.CountryName 
  FROM Country 
  WHERE (Country.ID Not In ([Forms]![Form1]![Combo1], 
                            [Forms]![Form1]![Combo2])) 
  ORDER BY Country.[CountryName]; 

Then set the GotFocus event for each combo box to requery its content as needed:

  Private Sub Combo1_GotFocus()
      Combo1.Requery
  End Sub

  Private Sub Combo2_GotFocus()
      Combo2.Requery
  End Sub

  Private Sub Combo3_GotFocus()
      Combo3.Requery
  End Sub