Why this strange behavior in listbox and collections ?

Question

Try make a form in Access with a listBox and insert this code into the form:

Dim colTest As New Collection

Private Sub Form_Load()
    Dim i As Long
    For i = 1 To 10000
        colTest.add i & " - " & String(i, "A")
    Next i
    MsgBox ("Col.Count = " & colTest.Count)
    
    For i = 1 To colTest.Count
        lbx.AddItem colTest.Item(i)
    Next i
    MsgBox ("Lbx.ListCount = " & lbx.ListCount)

End Sub

The list box can only hold around 25% of the collection !

I know a listbox with that large number of items is useless in practice ... but anyway ...

Why and how to get around this error ?

Answer 1

This is by design. If using Value List the data is a single string. There is a limit to the max length of that string.

Indeed it is not good UX to have a listbox or combobox with thousands of items.

---

-Tom. Microsoft Access MVP

Answer 2

I know that a listbox with so many item normally is useless in practice, BUT ...

I use a listbox in an Access form for showing items from a Windows explorer search and if that explorer search is - let's say - more than 350-380 items then the listbox can't hold the "collection of found items from the search window" and THAT's not good because it's crucial to present ALL found items for the user - it could be searching for frases in 10.000's of Word documents.

I use Windows explorer search because it finds items much, much, MUCH faster than any code I can mix together.

Answer 3

and THAT's not good because it's crucial to present ALL found items for the user - it could be searching for frases in 10.000's of Word documents.

Hi Ksor,

Knowing the limitation of the listbox, you could write that amount of data in a (temp)table.

Don't use a 4-seat car to transport a whole army.

Imb.

Answer 4

Should it solve the problem with the limit in the listbox - the records from the (temp)table should be presented to the user anyway.

IF that is THE solution I think it's strange the listbox have that limitation in the first place - but I'll try !

THX !

Answer 5

the records from the (temp)table should be presented to the user anyway.

Hi Ksor,

You could use a form, if necessary with sort and search possibilities, to display the table.

Imb.