Combobox ?

[paddymx]

Here's the deal. I've been using combo a lot so this is a new one for me.



I am selecting state abbrv's to a vendor table. As long as I click on the list everything goes correctly. But, the combo is not auto expanding as I enter a character and is giving me the "not in list" error even if I enter the correct abbrv. I've tried using an unbound combo and using the click event to populate the vendor field. That worked but as soon as I save the DB and re open it its back to the way it was acting even with the unbound approach. I have tried to turn off auto correct with no help. I've even tried a blank DB with just the involved tables and form. I then copied the working control to the original DB but it won't work there and the original test db stopped working as well(after closing and re opening). I've verified field sizes etc. and everything is OK.

I've double checked the options and start up options and everything is the same in both DB's.

Generally when I use combo's I save "ID" but this time I'm saving the two character abbrv's NC, CA etc. I fixed it so I don't get the error but now if I enter lower case and leave the combo ad re click the list it is aligned with the proper entry that it was telling me was not in the list. click on it and I now have the capitalized abbrv. Could this possibly be because I am saving the text instead of the ID?

I'm at a loss.

[June7]

Post the combobox properties:

ControlSource
RowSource
BoundColumn
ColumnCount
ColumnWidths

What is the data type of the bound field?

[paddymx]

Post the combobox properties:

ControlSource
VND_State IN VendorT
RowSource
SELECT States_T.Abbrv, States_T.State, States_T.ID FROM States_T ORDER BY States_T.Abbrv WITH OWNERACCESS OPTION; from StatesT
BoundColumn
1
ColumnCount
3
ColumnWidths
0.5";1";0"

What is the data type of the bound field?

VendorState is TXT, 3 in vendor table
State abbrv is TXT, 3 in state table

err 2237
dataerr 2113

[paddymx]

Just tried it with a query instead of a select statement, same result.

[paddymx]

Just tried it with a query instead of a select statement, same result.

just changed vendor state to a number in the vendor table, re built the combo, same result.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[June7]

If you want to save the state abbreviation instead of the ID autonumber, then set the abbreviation field as the primary key. The ID field is not needed in the RowSource.

Ooops - there are 4 records with the same state abbreviation - the four Armed Forces records. Need to resolve this before depending on the abbreviation for primary key and saving it as foreign key.

This is odd, none of the comboboxes AutoExpand is working. Never encountered this before.

I don't have Access 2003 available so don't know if I can help any more.

Bing: Access autoexpand not working

For one possible cause, review http://support.microsoft.com/kb/824189

[paddymx]

June7, Thanks for the time and effort. If this is strange to you then I don't feel so bad. I'll read the kb and experiment with the keys. Probably don't need the Armed Forces for this application.

I'm marking this resolved.

Edit: Forgot, "DISTINCT" did the trick! Additionally, searching for "auto expand not working" changes the way I'll use help. I was only looking for "Combobox". Never thought about looking for a feature. I'm going to do more research about this ANSI thing. I think I'll be using "Distinct" more in the future. Thanks again!

[paddymx]

June7, Thanks for the time and effort. If this is strange to you then I don't feel so bad. I'll read the kb and experiment with the keys. Probably don't need the Armed Forces for this application.

I'm marking this resolved.

Edit: Forgot, "DISTINCT" did the trick! Additionally, searching for "auto expand not working" changes the way I'll use help. I was only looking for "Combobox". Never thought about looking for a feature. I'm going to do more research about this ANSI thing. I think I'll be using "Distinct" more in the future. Thanks again!

Just thought I'd add an observation. Not only did DISTINCT solve the issue it also allowed for the duplicate state abbreviations (Armed Forces) to be displayed in the list. I could see this as an advantage if someones combo was looking for say "Last Names" where the possibility of duplicates is almost guaranteed. So, for the purposes of understanding we should maybe think of DISTINCT, in this case, as really "as distinct". I can see several benefits of this "quirk" of ANSI- 92! Had never really got into ANSI-92 before. It has always been part of my database options and never gave it much thought.