[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

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???
0
gaynes
Asked:
gaynes
1 Solution
 
Rick_RickardsCommented:
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

0
 
gaynesAuthor Commented:
Didn't work. Like I told you, one of them works, the one using the primary key. Other ideas?
0
 
TextReportCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rick_RickardsCommented:
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?
0
 
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.
http://support.microsoft.com/kb/824189
0
 
Rick_RickardsCommented:
Sounds like you got it.  Alwasy good to see an author who posts the solution they found.  No objection.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
modus_operandiCommented:
Closed, 500 points refunded.
modus_operandi
EE Moderator
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now