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

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:

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:If the RowSource property of the combo box does contain the previous query, then you can modify the RowSource property as follows:

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

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.

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.