ACC: LimitToList Enforced Even When Set to No

ID: Q122242

The information in this article applies to:

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 in the combo box, and then press ENTER. Note that you receive the error message stated above.


In Microsoft Access 2.0:
  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 in the combo box and then press ENTER. Note that you receive the error message stated above.



REFERENCES

For more information about the LimitToList property, search the Help Index for "LimitToList property," or ask the Microsoft Access 97 Office Assistant. 


Keywords          : kberrmsg FmsCmbo 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 8, 1999