ComboBox Limitation?

[DaLiMan]

Hi,

I have a form with > 100000 records.
Now the ComboBox on this form works not exactly as expected.
The Autofill works great but the pulldown menu does not show all the records....

is there a limitation on this list and better, is there a trick around this problem?

Thanx,
Daniel

 

[SydneyGeek]

Do you really want to display all the possible records? Your best option is to limit what displays so you get better performance, especially over a network.
However, if you want to load all of the into the combo when the form loads, you can place this into the Load event of the form:

Private Sub Form_Load()
  Dim lngCount As Long
  lngCount = yourCombo.ListCount
End Sub

Change yourCombo to suit; if you want to speed up several combos you will need to get the ListCount for each one in turn. The form will take longer to load but the combo will list all items.

Denis

 

[DaLiMan]

Hi and thanx,

The list is indeed working more properly but it limits as I now can see with record 65536.
After this the items are not shown....

Is there a workaround or tweak for this too?

 

[SydneyGeek]

Probably not, if you want to show the whole list. 65536 is one of those numbers that suggests a built-in limit.

Would it help to be able to filter the list down first, before displaying the remainder?

Denis

 

[SydneyGeek]

If you want to try a filtering trick, give this a go:

1. Normal combo
Standard combo, with the RowSource set to your big list

2. Fast Load
The option I mentioned before, using the Load event of the form to put the full list into the combo

3. Rapid Search
A hybrid that filters your list before populating the combo. The setup is as follows (pulling WordList from tblWords)

Textbox called txtSearch
Combo called cboRapidSearch

The combo's Rowsource is blank to start with

The AfterUpdate event of the textbox has this code:

Private Sub txtSearch_AfterUpdate()
    Dim lngCounter As Long
    If Len(txtSearch) > 0 Then
        cboRapidSearch.RowSource = "SELECT WordList FROM tblWords " _
            & "WHERE WordList LIKE '" & txtSearch & "*'"
    Else
        cboRapidSearch.RowSource = ""
    End If
    cboRapidSearch.Requery
    lngCounter = Me.cboRapidSearch.ListCount
End Sub

If you clear out the textbox, the rowsource for the combo is blank. Enter a couple of letters into the textbox, tab out of it, and the combo will have the list filtered down to words that start with the contents of the textbox.

Denis

 

[DaLiMan]

That's a nice tweak.
I must have a look how to implement this into the form for users are used of this.

With this new approach I understand I have to create a search in advance before populating the combobox....

Anyway, thanx a lot for this help and I will sure give it a try!

Daniel

 

[SydneyGeek]

Daniel,

With this new approach I understand I have to create a search in advance before populating the combobox....

That is a limitation but at least your users can get to every item in the list this way. And it should improve performance too, because you will only ever load a subset.

Denis