ACC: LimitToList Enforced Even When Set to No

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

When you type a unique value in a combo box and then press ENTER, you may receive the following error message even though the combo box's LimitToList property is set to No:

   In Microsoft Access 7.0 and 97:

      The text you entered isn't an item in the list

   In Microsoft Access 2.0:

      The text you enter must match an entry in the list

CAUSE

Even if the combo box's LimitToList property is set to No, Microsoft Access limits entries to those in the combo box if the first column displayed in the combo box is not the bound column. This behavior occurs because Microsoft Access stores the bound column value when you enter a nonbound column selection. If you could enter a new value for the nonbound column, Microsoft Access would have no value to store for the bound column.

RESOLUTION

In order to allow unique entries in a column box, make the first nonhidden column in the combo box the bound column.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

In Microsoft Access 7.0 and 97:

1. Open the sample database Northwind.MDB.

2. Create a new query and select Design View. Add the Products table. Add the following fields to the QBE grid:

         Field: ProductName
            Show: True
   
         Field: ProductID
            Show: True
   
   

3. Save the query as Test Query1

4. Create a new form not based on any table or query.

5. Add a combo box with the following properties to the form:

         RowSourceType: Table/Query
         RowSource: Test Query1
         ColumnCount: 2
         BoundColumn: 2
         LimitToList: No
   

   Note that the bound column is column 2, which contains the ProductID field. The ProductName field will appear in the first column.

6. View the form in Form view.

7. Type "New Cola" (without the quotations marks) in the combo box, and then press ENTER. Note that you receive the error message stated above.

1. Open the sample database NWIND.MDB.

2. Create a new form not based on any table or query.

3. Add a combo box with the following properties to the form:

         RowSourceType: Table/Query
         RowSource: Product Names and IDs
         ColumnCount: 2
         BoundColumn: 2
         LimitToList: No
   

   Note that the bound column is column 2, which contains the Product ID field. The Product Name field will appear in the first column.

4. View the form in Form view.

5. Type "New Cola" (without the quotations marks) in the combo box and then press ENTER. Note that you receive the error message stated above.

REFERENCES