The AutoExpand property may not work when ANSI-92 syntax is enabled and the Combo Box RowSource does not use DISTINCT values (824189)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article applies only to a Microsoft Access database (.mdb).

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

SYMPTOMS


When you enable ANSI-92 SQL syntax in a database, and the RowSource property for a combo box on the form does not include the DISTINCT keyword, the AutoExpand feature of the combo box may not work.

In addition, if you type the value that is a part of the list, and then you shift the focus to another control on the form, you may receive the following error message:

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

Select an item from the list, or type the text that matches one of the listed items.

WORKAROUND

To work around this problem, add the DISTINCT keyword to the RowSource property of the combo box.

For example, the RowSource property of the combo box may contain the following query:
SELECT EmployeeID, LastName, FirstName FROM Employees;
If the RowSource property of the combo box does contain the previous query, then you can modify the RowSource property as follows: 
SELECT DISTINCT EmployeeID, LastName, FirstName FROM Employees;

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Problem

  1. Start Microsoft Access.
  2. Open the Northwind sample database.
  3. On the Tools menu in the Database window, click Options.
  4. On the Tables/Queries tab in the Options dialog box that you find under SQL Server Compatible Syntax (ANSI 92), click to select the This database check box.
  5. Click OK.
  6. Create a new form in Design view.
  7. Add a combo box to the form, and then set the following properties:

    Combo Box
    -------------------------
    Name: ComboTest
    Bound Column: 1
    RowSourceType: Table/Query
    Row Source: SELECT EmployeeID, LastName, FirstName FROM Employees;
    Auto Expand: Yes
    Limit To List: Yes
    Column Count: 3
    Column Widths: 0";1";1"

  8. Save the form, and then close the form.
  9. Open the form in Form view.
  10. In the ComboTest box, type Peacock, and then press ENTER.

    When you try to type Peacock in the ComboTest box, the AutoExpand property for the ComboTest box does not work. Additionally, when you press ENTER to move the focus from the combo box, then you receive the error message that is in the "Symptoms" section.

REFERENCES

For more information about the AutoExpand property, click Microsoft Access Help on the Help menu, type AutoExpand property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:6/8/2004
Keywords:kbprb kbEditCtrl kbDatabase kbComboBox kbProperties kberrmsg KB824189 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.