Speeding up sluggish listboxes

[salad]

This is a tip on how to speed up listboxes DRAMATICALLY. Persons that
would benefit are those that are constantly updating the rowsource of a
listbox/combobox in order to filter and sort the data and the refreshes
are slow.

(OT. I've often wondered why there is no .Sort or .Filter property for
Combos and Listboxes.)

My listboxes , and their rowsources, on my form were constantly being
refreshed to filter and sort data. Ex:
Me.ListBox.Rowsource = "Select * From Test Where LastName = "Smith"
Me.ListBox.Rowsource = "Select * From Test Where LastName = "Jones"
This method of constantly updating the rowsource to sort and filter the
data in code was a real drag on speed.

To speed things up I converted the RowType to a function, not Table/Query.

Normally I wouldn't be posting something like this post, but the result
of switching to a RowType function was most impressive.

It isn't hard to create a user-defined listbox function but it may take
some study. Look at the code examples for a RowType function in help to
get you started.

I defined a type in the declarations section and a global recordset
definition in the Declarations section of the form.
Private Type ListElements
'only 1 element for brevity. Used for listbox column data
strDesc As String
End Type
Dim rstGlobal As Recordset

Everytime records were added or modified and the listboxes needed to be
requeried or sorted, I would refresh the recordset
Set rstGlobal = Currentdb.Openrecordset("Select ....
I now had the most current recordset to display all records.

I would now do something in the "Initialize" section of the function
similar to the following code.
Case acLBInitialize
'the following are the key concepts
Dim Entries As Integer
Static strRows() As ListElements
Dim rstFilter As Recordset
rstGlobal.Sort = "[ItemDescription]"
rstGlobal.Filter = "ItemNum = 10"
Set rstFilter = rstGlobal.OpenRecordset

If rstFilter.RecordCount 0 Then
rstFilter.MoveFirst
Do While Not rstFilter.EOF
Entries = Entries + 1
ReDim Preserve strRows(Entries)

strRows(Entries).strDesc = rstFilter!ItemDesc
rstFilter.MoveNext
Loop
Endif

I would use the data contained in strRows to fill in the column data in
the function.

The net result of moving away from
Me.ListBox.Rowsource = "...
to calling a function in the Rowtype
RowType : ListBoxDataDisplay
and using
Me.ListBox.Requery
has gone from slow refreshes to instantaneous refreshes. The speed
increase has been mindblowing.

I would not use the above method for setting the rowsource of your
typical listbox. However, if this is a major form in your application
and you are constantly filtering/sorting the data in the listbox, it
would be worth your time to update the RowType to a function.

[Tom van Stiphout]

On Thu, 21 Dec 2006 09:54:53 GMT, salad <oi*@vinegar.comwrote:

Interesting.
Improve this even further by taking Redim Preserve out of the loop:
If rstFilter.RecordCount 0 Then
rstFilter.MoveLast
ReDim strRows(rstFilter.RecordCount)
rstFilter.MoveFirst
Do While Not rstFilter.EOF
Entries = Entries + 1

strRows(Entries).strDesc = rstFilter!ItemDesc
rstFilter.MoveNext
Loop
Endif

If this were an ADO recordset, one could probably write a one-liner
using GetString and Split.

-Tom.

>This is a tip on how to speed up listboxes DRAMATICALLY. Persons that
would benefit are those that are constantly updating the rowsource of a
listbox/combobox in order to filter and sort the data and the refreshes
are slow.

(OT. I've often wondered why there is no .Sort or .Filter property for
Combos and Listboxes.)

My listboxes , and their rowsources, on my form were constantly being
refreshed to filter and sort data. Ex:
Me.ListBox.Rowsource = "Select * From Test Where LastName = "Smith"
Me.ListBox.Rowsource = "Select * From Test Where LastName = "Jones"
This method of constantly updating the rowsource to sort and filter the
data in code was a real drag on speed.

To speed things up I converted the RowType to a function, not Table/Query.

Normally I wouldn't be posting something like this post, but the result
of switching to a RowType function was most impressive.

It isn't hard to create a user-defined listbox function but it may take
some study. Look at the code examples for a RowType function in help to
get you started.

I defined a type in the declarations section and a global recordset
definition in the Declarations section of the form.
Private Type ListElements
'only 1 element for brevity. Used for listbox column data
strDesc As String
End Type
Dim rstGlobal As Recordset

Everytime records were added or modified and the listboxes needed to be
requeried or sorted, I would refresh the recordset
Set rstGlobal = Currentdb.Openrecordset("Select ....
I now had the most current recordset to display all records.

I would now do something in the "Initialize" section of the function
similar to the following code.
Case acLBInitialize
'the following are the key concepts
Dim Entries As Integer
Static strRows() As ListElements
Dim rstFilter As Recordset
rstGlobal.Sort = "[ItemDescription]"
rstGlobal.Filter = "ItemNum = 10"
Set rstFilter = rstGlobal.OpenRecordset

If rstFilter.RecordCount 0 Then
rstFilter.MoveFirst
Do While Not rstFilter.EOF
Entries = Entries + 1
ReDim Preserve strRows(Entries)

strRows(Entries).strDesc = rstFilter!ItemDesc
rstFilter.MoveNext
Loop
Endif

I would use the data contained in strRows to fill in the column data in
the function.

The net result of moving away from
Me.ListBox.Rowsource = "...
to calling a function in the Rowtype
RowType : ListBoxDataDisplay
and using
Me.ListBox.Requery
has gone from slow refreshes to instantaneous refreshes. The speed
increase has been mindblowing.

I would not use the above method for setting the rowsource of your
typical listbox. However, if this is a major form in your application
and you are constantly filtering/sorting the data in the listbox, it
would be worth your time to update the RowType to a function.

[salad]

Tom van Stiphout wrote:

On Thu, 21 Dec 2006 09:54:53 GMT, salad <oi*@vinegar.comwrote:

Interesting.

I was amazed by the difference in speed. I did some visual start/stop
time tests on loading the listbox rowsource with a new rowsource. It
was easy to see the drag on the update.

I wondered if someone knew if using a funtion was faster than updating
the rowsource with a new query...but I figured someone would say
"check it out and get back to us."

I figured since updating the data in a function was doing more things
in memory, it should be faster. I was correct.

But...for the most part creating a listbox function isn't necessary.
But it sure is useful for those special occasions.

Improve this even further by taking Redim Preserve out of the loop:
If rstFilter.RecordCount 0 Then
rstFilter.MoveLast
ReDim strRows(rstFilter.RecordCount)
rstFilter.MoveFirst
Do While Not rstFilter.EOF
Entries = Entries + 1

strRows(Entries).strDesc = rstFilter!ItemDesc
rstFilter.MoveNext
Loop
Endif

Done. :-)

If this were an ADO recordset, one could probably write a one-liner
using GetString and Split.

-Tom.

>>This is a tip on how to speed up listboxes DRAMATICALLY. Persons that
would benefit are those that are constantly updating the rowsource of a
listbox/combobox in order to filter and sort the data and the refreshes
are slow.

(OT. I've often wondered why there is no .Sort or .Filter property for
Combos and Listboxes.)

My listboxes , and their rowsources, on my form were constantly being
refreshed to filter and sort data. Ex:
Me.ListBox.Rowsource = "Select * From Test Where LastName = "Smith"
Me.ListBox.Rowsource = "Select * From Test Where LastName = "Jones"
This method of constantly updating the rowsource to sort and filter the
data in code was a real drag on speed.

To speed things up I converted the RowType to a function, not Table/Query.

Normally I wouldn't be posting something like this post, but the result
of switching to a RowType function was most impressive.

It isn't hard to create a user-defined listbox function but it may take
some study. Look at the code examples for a RowType function in help to
get you started.

I defined a type in the declarations section and a global recordset
definition in the Declarations section of the form.
Private Type ListElements
'only 1 element for brevity. Used for listbox column data
strDesc As String
End Type
Dim rstGlobal As Recordset

Everytime records were added or modified and the listboxes needed to be
requeried or sorted, I would refresh the recordset
Set rstGlobal = Currentdb.Openrecordset("Select ....
I now had the most current recordset to display all records.

I would now do something in the "Initialize" section of the function
similar to the following code.
Case acLBInitialize
'the following are the key concepts
Dim Entries As Integer
Static strRows() As ListElements
Dim rstFilter As Recordset
rstGlobal.Sort = "[ItemDescription]"
rstGlobal.Filter = "ItemNum = 10"
Set rstFilter = rstGlobal.OpenRecordset

If rstFilter.RecordCount 0 Then
rstFilter.MoveFirst
Do While Not rstFilter.EOF
Entries = Entries + 1
ReDim Preserve strRows(Entries)

strRows(Entries).strDesc = rstFilter!ItemDesc
rstFilter.MoveNext
Loop
Endif

I would use the data contained in strRows to fill in the column data in
the function.

The net result of moving away from
Me.ListBox.Rowsource = "...
to calling a function in the Rowtype
RowType : ListBoxDataDisplay
and using
Me.ListBox.Requery
has gone from slow refreshes to instantaneous refreshes. The speed
increase has been mindblowing.

I would not use the above method for setting the rowsource of your
typical listbox. However, if this is a major form in your application
and you are constantly filtering/sorting the data in the listbox, it
would be worth your time to update the RowType to a function.