Combo box Auto Expand not working - Access 2007

[Gary]

In a large application I have many combo boxes with Auto Expand
property set to "Yes". There are two fields in the row source:

SELECT DISTINCTROW TBL_TOWN.TCODE, TBL_TOWN.TNAME
FROM TBL_TOWN
ORDER BY TBL_TOWN.TNAME;

First visible field is TNAME.
Bound column = 1

The combo boxes worked fine in Access 97. But after converting to
Access 2007, the auto expand feature is not working.

I've deleted one of the existing combo boxes and have recreated it
using the wizard. Afterwards, Auto Expand is working. But when I save/
close the form and then reopen it, Auto Expand is no longer working. I
see that the combo box is in fact the newly created object. But Auto
Expand is failing. All property settings of the combo box are still
correct (unchanged from those set by the wizard).

I'd be most grateful for any suggestions.

 

[Gary]

In a large application I have many combo boxes with Auto Expand
property set to "Yes". There are two fields in the row source:

SELECT DISTINCTROW TBL_TOWN.TCODE, TBL_TOWN.TNAME
FROM TBL_TOWN
ORDER BY TBL_TOWN.TNAME;

First visible field is TNAME.
Bound column = 1

The combo boxes worked fine in Access 97. But after converting to
Access 2007, the auto expand feature is not working.

I've deleted one of the existing combo boxes and have recreated it
using the wizard. Afterwards, Auto Expand is working. But when I save/
close the form and then reopen it, Auto Expand is no longer working. I
see that the combo box is in fact the newly created object. But Auto
Expand is failing. All property settings of the combo box are still
correct (unchanged from those set by the wizard).

I'd be most grateful for any suggestions.

After much testing and research, I discovered why Auto Expand property
of my combo boxes was failing:

Office Button... Access Options... Object Designers...SQL Server
Compatible Syntax (ANSI 92)

ANSI 92 must be unchecked.

Found the solution in a 9/19/96 post the news group. I'm copying in
that post below.
:

Access 2003.

Auto complete was working with a combo box. After adding much
functionality
to a database I find that auto complete does not work on any combobox
in any
form. So I create a blank database and a simple test shows that auto
complete
works. When a form from the faulty database is imported in the test
database,
auto complete stops working on the test form. Even if the imported
faulty
form is removed, auto complete still does not work.

I tried creating a new database and importing the faulty form after
removing
all controls and events from the form except for the combo box. Auto
complete
still stops working. Not only on the imported form but also on the
existing
form which works prior to the import.

I checked auto expand and it is 'Yes' in all conditions.

What ever is causing the problem on the faulty form seems to cause
some
property of the database to change.

Is there some database option or property setting that controls auto
complete?

Is is possible that importing a form will cause a database property to
change?

--
RobGMiller

Hum, something is wrong here...just not sure what....

first, does the combo box sql have the order set? Data kind of needs to be
in order for it really to work....

I would also turn off track-autoname correct (tools->options).

I assume that the base stuff, like the code compiles, and compact/repair has
been done....

I would also disable the "allow autocorrect" in the "other" tab of the combo
box (this is for the spell checker..and you don't want it messing with
stuff).

And, perhaps you turned on ansi92 compatibility?

http://support.microsoft.com/?kbid=824189

:


Thanks for your reply and the solution.

Ansi92 was turned on and 'Distinct' was not used in SQL row source.

It is remarkable that importing a broken form into a database where
ansi92
compliance was not turned on broke an existing form wihtout turning
on
ansi92.

In any case, the issue is resolved. Thanks again.