Slow processing of recordset after change of current record.

[papadilbert]

I have a form "aForm" that that is bound to a table "aTable". aForm displays a row of aTable. The record selector is used to move through the rows.

Since I have about 2500 rows in the table, I created a popup listbox that lists 2 principle fields, 500 rows at a time. The user clicks on an item and the recordset.AbsolutePosition is updated. There is a Previous and Next button to list the previous or next 500 rows.

Before the user clicks on an item in the listbox, displaying the previous and next 500 items is fast. (Works great)

When the user selects an item (updating recorset.AbsolutePosition), clicking previous or next is extremely slow.

I coded a Debug.Print to monitor the "next" process. I found that processing started at a normal rate but that each record processed in the recordset (adding it to the listbox) became slower and slower, to the point that the last 100 items were taking close to a second each to add.

One solution is to close the listbox after the user selects an item, forcing the user to re-pop the selection listbox. But that kind of defeats the usefulness.

Why is the recordset processing slowing more with each record processed? Or is it the add to the listbox that is slowing?

 

[papadilbert]

This is just plain weird (for the less intuitive).

In the slowdown scenario I commented out the addItem. Processing the recordset was no longer slowed.

So I've narrowed down the problem to be related to listbox.additem.

Thinking that it might be related to having an item selected (even though I clear the listbox with listbox.RowSource = "" before repopulating the listbox), I tried unselecting the item with:
listbox.Selected(i) = False
before setting RowSource = "".

Still, AddItem slows with each item added.

I'm stumped.

 

[papadilbert]

I'm still not sure why AddItem slows but I've now recoded the routine that populates the listbox, avoiding use of AddItem, and instead, building a string of values in a loop and then setting RowSource to the string.

But the question remains unanswered. Why does AddItem slow with each add only after an item has been selected?