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.Rows ource = "Select * From Test Where LastName = "Smith"
Me.ListBox.Rows ource = "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.Openr ecordset("Selec t ....
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.Filte r = "ItemNum = 10"
Set rstFilter = rstGlobal.OpenR ecordset
If rstFilter.Recor dCount 0 Then
rstFilter.MoveF irst
Do While Not rstFilter.EOF
Entries = Entries + 1
ReDim Preserve strRows(Entries )
strRows(Entries ).strDesc = rstFilter!ItemD esc
rstFilter.MoveN ext
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.Rows ource = "...
to calling a function in the Rowtype
RowType : ListBoxDataDisp lay
and using
Me.ListBox.Requ ery
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.
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.Rows ource = "Select * From Test Where LastName = "Smith"
Me.ListBox.Rows ource = "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.Openr ecordset("Selec t ....
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.Filte r = "ItemNum = 10"
Set rstFilter = rstGlobal.OpenR ecordset
If rstFilter.Recor dCount 0 Then
rstFilter.MoveF irst
Do While Not rstFilter.EOF
Entries = Entries + 1
ReDim Preserve strRows(Entries )
strRows(Entries ).strDesc = rstFilter!ItemD esc
rstFilter.MoveN ext
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.Rows ource = "...
to calling a function in the Rowtype
RowType : ListBoxDataDisp lay
and using
Me.ListBox.Requ ery
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.
Comment