Auto Expand not working in Combo boxes

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

Select allOpen in new window

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

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?

Sounds like you got it.  Alwasy good to see an author who posts the solution they found.  No objection.

gaynes,

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.

JeffCoachman

Closed, 500 points refunded.
modus_operandi
EE Moderator