473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Speeding up sluggish listboxes

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.
Dec 21 '06 #1
2 4308
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.
Dec 21 '06 #2
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.

Dec 21 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Jeffrey Barish | last post by:
I have an application that produces two listboxes. I would like to be able to select one of the items in the first listbox and one of the items in the second listbox. However, when I make my...
3
by: Simon Templar | last post by:
I need the following functionality: With 2 listboxes populated from a database with the SAME data, I need any of the listboxes to stop displaying the option when selected at the other listbox. Eg:...
9
by: Susan Bricker | last post by:
Hi. I have two questions ... (1) I want to use a Listbox to enable the user to select 1 or many items from the list. However, I'm having trouble figuring out how to find out t which items have...
4
by: bill yeager | last post by:
I have several template columns inside of a datagrid. Inside of these template columns are databound listboxes: <asp:TemplateColumn HeaderText="Crew Chiefs"> <ItemTemplate> <asp:listbox...
0
by: Terry D | last post by:
I'm having an issue with an ASP.NET page (VS.NET 2003, VB.NET, Oracle back end). The page uses the standard VS.NET grid to display the records from a particular table. The user can edit certain...
1
by: Ryan Ternier | last post by:
I have two listboxes, and allow users to move items between them via the following function: function SwitchList(fbox, tbox){ var arrFbox = new Array(); var arrTbox = new Array(); var...
0
by: Luis Esteban Valencia | last post by:
have a problem and I'm not sure how to handle/fix it. I have three listboxes on my page. The first listbox has a list of software products. When you select an item in the Products listbox, then...
10
by: Sarah Smith | last post by:
Hello, I am a bit of a newbie to VB.NET, but not totally new. I took the plunge recently and decided (along with my colleagues), to try to convert/port a VB6 client/server app to .Net. (I'm...
4
by: Steve Richter | last post by:
using VS2005 to build a web site. The more pages I add to the web site, the more sluggish visual studio becomes. I have around 20 pages now and when I press F5 to test a page it can take over 10...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.