AutoExpand

[Spider Jack]

I am using Access 2003 and I am having a problem with
combo boxes. Here is the SQL for my row source:
SELECT TableID, TextName FROM SomeTable

I set the bound column to 1 (TableID), but set the column
widths = "0 in" Therefore, the only thing that shows up
is the TextName value. The user does not need to see the
ID field, just the text field. In previous versions of
Access the AutoExpand property did what it said. It
seems in 2003 it is only checking against the bound
column, and only if it is visible.

Anybody know how to get 2003 to correctly AutoExpand even
when the bound column is not visible?

 

[Allen Browne]

Works fine here.
You did remember to set the Column Count to 2?

 

[Spider Jack]

Yes the column count is two, LimitToList is True, and
AutoExpand is True.

 

[Spider Jack]

If I set the RowSource for a Value List, then it works
fine, but if I set the RowSource for Table/Query then it
fails and autoexpand does not work.

 

[Allen Browne]

It should work, so tell us a bit more to help identify why it's failing.

How many records are in the RowSource?

Is the RowSource a table? Saved query? SQL statement?

What kind of table: within this mdb? a linked Access table? a linked ODBC
table?
On a LAN? WiFi connection?

What settings do you have under Tools | Options | Edit/Find for:
- Local indexed fields?
- Local non-indexed fields?
- ODBC fields?
- Don't display lists where more than ...?

 

[Spider Jack]

The number of records does not matter, it could be 1 or
100.
The RowSource is a saved query.
The table is within this mdb, and the file is local not
stored on a LAN.
Local indexed fields = True
Local nonindexed fields = True
ODBC fields = False
Don't display ... = 1000

Another note to add to the symptoms. If I create a new
table and set a foreign key field to Lookup Wizard, and
go through that process, then the combo box works
perfectly, until I restart Access, then it stops working.

 

[Allen Browne]

Okay, none of the symptoms indicate a problem with any settings, and you
have a strange situation where it starts and does not continue working after
a restart.

Create a new (blank) database.
Turn off the Name AutoCorrect options.
If you can not duplicate the symptoms there, the mdb must be damaged
(partially corrupt).

If the issue persists in the new mdb, it must be a problem with the Access
installation. Uninstall, and reinstall Office.

 

[Spider Jack]

This is with every mdb file, new or not. I have Office
installed on a laptop and a desktop, both versions are
doing the same thing.

So that is two separate installations.

 

[Allen Browne]

Jack, have just discovered this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;824189&Product=acc2003

Title:
The AutoExpand Property May Not Work When ANSI-92 Syntax Is Enabled and the
Combo Box RowSource Does Not Use DISTINCT Values

Could this apply?