Auto Expand not working in Combo boxes

I have a weird problem. I am wondering if it is SQL Server 2005 related. I have 4 combo boxes. 1 autoexpands fine (this is the key field). The other 3 are set up exactly the same but will not auto expand. The fields in the other 3 are indexed. These are used for lookup and it works if you use the pull-down and choose or type in the value. In a pre-sql version, before exporting the tables, the auto expand worked. Ideas???
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This is probbably happening because there are enough values (most likely more than 200) in the other combo boxes and SQL server in an attempt to avoid sending the entire recordset over the network is supplying enough data to the combo box to get it started.  For the AutoExpand to work propertly it needs all of the data.

You can get the combo box to insist upon getting all the data from SQL server by asking a question of it that will require it to go get all that is there.  One simple way to do this is by adding a little code as seen below to the Form's Load event.  In order for it to send the .ListCount it will have to get all the data so it knows how many items there are in the list.  Doing this will cause you to take a performance hit as you're defeating what SQL server is trying to do, (basically it's trying to speed things up by supplying a partial data set until more is required).  That said the performance hit may or may not be noticable.
Private Sub Form_Load()
    Debug.Print Me.cboMyComboBox.ListCount
End Sub

Open in new window

gaynesAuthor Commented:
Didn't work. Like I told you, one of them works, the one using the primary key. Other ideas?
Hi gaynes, not sure from your description of the property that your understanding of the auto expand is correct. Please also check the LimitToList setting for each control.

Taken from the Access 2000 help fil
"You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters you enter as you type in the combo box. This lets you quickly enter an existing value in a combo box without displaying the list box portion of the combo box.

When you enter characters in the text box portion of a combo box, Microsoft Access searches the values in the list to find those that match the characters you have typed. If the AutoExpand property is set to Yes, Microsoft Access automatically displays the first underlying value that matches the characters entered so far.

When the LimitToList property is set to Yes and the combo box list is dropped down, Microsoft Access selects matching values in the list as the user enters characters in the text box portion of the combo box, even if the AutoExpand property is set to No. If the user presses ENTER or moves to another control or record, the selected value appears in the combo box."

Cheers, Andrew
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Another possibility is that it's not autoexpanding because the value entered so far actually matches the value of one of the records within the list.  For example, if you had a table with the values 0 through 255 entered in it and you typed 25 it would not auto expand to any of the values 250 through 255 because the value 25 is a hit (matching value) of one of the rows within the list.  If however, you added a WHERE clause to the query and excluded the value 25 from the list of values, when you typed 25 it would then autoexpand to one of the other possible values that would match (250 through 255 - the exact value that would appear would depend on the sort order of the records).  

Is it possible that AutoExpand is neglecting to AutoExpand because the numeric value entered thus far actually matches one of the values within the list?
gaynesAuthor Commented:
I found the answer on the MS website. I had to set the SQL to DISTINCT and it worked. Thanks for trying. Here's the link for future reference.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sounds like you got it.  Alwasy good to see an author who posts the solution they found.  No objection.
Jeffrey CoachmanMIS LiasonCommented:

Just keep in mind that when using DISTINCT, it must check the entire list.
If your source table has lots of records, this might take a while.

Closed, 500 points refunded.
EE Moderator
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.