Microsoft KB Archive/824189

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

Article ID: 824189

Article Last Modified on 6/8/2004

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-





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.



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.</li> Create a new form in Design view.</li> 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&quot;;1&quot;;1&quot;

</li> Save the form, and then close the form.</li> Open the form in Form view.</li> 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 &quot;Symptoms&quot; section.</li></ol>

<div class="references_section">