ACC2: Cannot Clear Selection in LimitToList Combo Box

ID: Q123737

The information in this article applies to:

Microsoft Access 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

For example, in the sample database, NWIND.MDB, you can call the AdjustEmptyCombo procedure from the Change event of the Salesperson combo box on the Orders form:


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

You must pass the combo box control as an argument to the AdjustEmptyCombo procedure.

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

Open the sample database, NWIND.MDB.

Open the Orders form.

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.

To work around this behavior, do the following:

Create a new module with the following in the Declaration section:
```
      Option Explicit 
```

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

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

Open the Orders form in Design view.

View the properties for the Salesperson combo box.

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 
```

Add the following line to the procedure code:


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

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

REFERENCES

For more information about the NotInList event, search for "NotInList," and then "NotInList Event" using the Microsoft Access Help menu.

Keywords : kberrmsg kbusage FmsCmbo
Version : 2.0
Platform : WINDOWS
Issue type : kbprb