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

View products that this article applies to.

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.

Back to the top

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;

Back to the top

STATUS

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

Back to the top

MORE INFORMATION

Steps to Reproduce the Problem

Start Microsoft Access.
Open the Northwind sample database.
On the Tools menu in the Database window, click Options.
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.
Click OK.
Create a new form in Design view.
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"
Save the form, and then close the form.
Open the form in Form view.
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.

Back to the top

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.

Back to the top