ACC2: Cannot Clear Selection in LimitToList Combo Box

• Microsoft Access version 2.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you delete the selected entry in a combo box, you may receive the following error message:

   The text you enter must match an entry in the list.

CAUSE

The combo box's LimitToList property is set to Yes. When a combo box's LimitToList property is set to Yes, an empty selection does not match any value in the list. This behavior makes clearing a selection in the combo box difficult.

RESOLUTION

You can work around this behavior by including the following Access Basic procedure in a new or existing module in your database and calling it from the Change event of your combo box:

   Sub AdjustEmptyCombo (C As Control)
      On Error Resume Next
      If IsNull(C.Text) Or C.Text = "" Then C = Null
   End Sub

   Sub Employee_ID_Change ()
      AdjustEmptyCombo Me![Employee ID]
   End Sub

NOTE: This technique will not work if the field the combo box is bound to is a Required field. For more information about Required fields, search for "Required" then "Required Property" using the Microsoft Access 2.0 Help menu.

STATUS

This behavior no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database, NWIND.MDB.

2. Open the Orders form.

3. Click into the Salesperson combo box and delete the selection, and then press ENTER.

   Result: The following message appears:

         The text you enter must match an entry in the list.
   

1. Create a new module with the following in the Declaration section:

         Option Explicit
   

2. Create the following procedure:

         Sub AdjustEmptyCombo (C As Control)
            On Error Resume Next
            If IsNull(C.Text) Or C.Text = "" Then C = Null
         End Sub
   
   

3. Save the module with a unique name, and then close the module.

4. Open the Orders form in Design view.

5. View the properties for the Salesperson combo box.

6. Choose [Event Procedure] for the OnChange property and choose the Build button to the right of the property setting to view the empty Change event procedure:

         Sub Employee_ID_Change ()
   
         End Sub
   
   

7. Add the following line to the procedure code:

         Sub Employee_ID_Change ()
            AdjustEmptyCombo Me![Employee ID]
         End Sub
   
   

8. Repeat the steps in the "Steps to Reproduce Behavior" section. Note that you are able to clear the selection without an error.

REFERENCES