I have an Access form containing a scrolling listbox named 1stSearch that displays over 6,500 records. At the top of each column in the list are buttons used for sorting its records.
When I scroll down the listbox to try to reach the last record, the scroll is very slow. Many "pulls" of the elevator button are required to get to the bottom. To speed this up, I've added some VBA code that selects and deselects a record near the bottom of the list, and then does the same to the record at the top. (This seems to give Access an idea of the size of the list; in any case the list does then scroll normally.)
The problem is that the code doesn't get carried out when the form loads and the list populates. To get the fast scroll to work, I have to first click one of the sorting buttons--after the list re-sorts the scrolling is fast, but if I try scrolling without clicking a sort button, the scrolling is slow. I'd like to have the list scrollable fast as soon as the form appears.
Here's the pertinent portion of the code I'm using:
Code:
Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String
'Set row source for list box
strSQL = "SELECT DISTINCTROW PersonID, LastName, FirstName, MiddleName, DeathDate "
strSQL = strSQL & "FROM AllDeathRecords "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!lstSearch.RowSource = strSQL
'Increase 1stSearch listbox scroll speed by selecting last, then first, rows.
Me!lstSearch.Selected(lstSearch.ListCount - 6500) = True
Me!lstSearch.Selected(lstSearch.ListCount - 6500) = False
Me!lstSearch.Selected(lstSearch.ListCount - 1) = True
Me!lstSearch.Selected(lstSearch.ListCount - 1) = False
End Function
Is there a way to have the select/deselect code carried out before the user interacts with the form, or tries to scroll the list?