List Box Overflow Error (Integer Declaration Limitation)

[PSUIVERSON]

I am populating a list box with roughly 35,000 records. I put in a search functionality that looks for the account number and highlights it for the user.

However, when it hits record i value 32,768 in the list index it gives me an overflow error.

Is the list box index limited to the integer range?

Here is my code: I bolded where I get the <Overflow> error:

Private Sub cmdSearch_Click()

Dim searchText As String

Dim adoConnection As ADODB.Connection
Dim rst As ADODB.Recordset
Dim connectString As String

' Create a new connection
Set adoConnection = New ADODB.Connection

' Creat a new Recordset
Set rst = New ADODB.Recordset

' Build connection string
connectString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\SOM Files\FINSYS WORK\SOMM_ConversionProcess.mdb&quot;

adoConnection.Open connectString

' Populate Values into List Box

rst.Open &quot;Finsys_Populate&quot;, adoConnection, adOpenKeyset, adLockReadOnly

searchText = Left(mskSearch.Text, 3) & Right(mskSearch.Text, 4)

rst.MoveFirst
Do While Not rst.EOF
If rst!area & rst!orgn = searchText Then
GoTo keepGoing
End If
rst.MoveNext
Loop

MsgBox (&quot;Please enter valid search criteria. Entry not found.&quot
Exit Sub

keepGoing:

' Overflow ERROR --> Think List box Index maxes out
For i = 1 To 50000
If searchText = Left(lstAcct.List(i), 7) Then
lstAcct.Selected(i) = True
GoTo FoundExit
End If
Next i

MsgBox (&quot;Your selection was not found. Please reenter and search again.&quot

FoundExit:

End Sub

 

[Spellman]

try at the start of the function declaring i as long and see if that helps any.

Mark

The key to immortality is to make a big impression in this life!!

 

[PSUIVERSON]

No luck.

I had declared i as Double globally.

It's really strange. When I trap the error and glance over i it shows 32,768 - which tells me i is not having problems. But when you rollover lstAcct.list the message reads <Overflow>.

Hope someone knows or I'll have to reengineer the search tool I guess.

 

[CCLINT]

A list box with that many records is not what a list box is intended for.
You have reached the list box's maximum capability (ListIndex is an short integer).

You might rather use a DataGrid or HFlexGrid for this, or force the user to limit the amount of records in the criteria (SELECT TOP 5000 FROM SomeTable WHERE ...)

I couldn't imagine a user needing to scroll through all 32-thousand-plus records - esp. in a listbox...

 

[PSUIVERSON]

CCLINT -

Understood. It's a giant corporation with a massive amount of account numbers that each have extensive detail. The concept was when a user selects a search criteria they can see all the similar sorted accounts in the same ballpark and their YTD balances etc.

I assumed it was over the list box limit...I guess I was confused as to how the list box populated itself initially with over the max number but when you search the index in code it is maxed out?

I was considering Flexgrid or XP Spreadsheet component to populate and then use in that manner.

Guess that's the route I have to go now...

thanks again for confirmation.

 

[CCLINT]

>you search the index in code it is maxed out?

As I said, ListIndex is an short integer, and this is the problem.

put your cursor on the word &quot;ListIndex&quot; or &quot;List&quot; as in:

List1.ListIndex

and press F2.
The object browser comes up on that property.

Look at the botton of the window where the syntax is displayed and see the type the ListIndex is.
Do the same for the List1.List and for List1.ListCount


You might want to use for now a DataList. I THINK it doesn't have this limitation.
This gets it's row source data from a recordset, so you can do selecting and searching with the recordset object instead.
You don't have to use it to update a field or whatever, but just as a list.