Results 1 to 3 of 3
  1. #1
    wardw is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41

    Can't trigger VBA code on form load

    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?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Have you tried calling that function in the form's load event?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    wardw is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41
    Thanks for the suggestion, Paul; I wasn't sure how to do that, but I figured it out and now the list scrolls properly when the form loads. It initially displays the last records (scrolled all the way down), so I changed the code to
    Code:
        Me!lstSearch.Selected(lstSearch.ListCount - 1) = True
        Me!lstSearch.Selected(lstSearch.ListCount - 1) = False
        Me!lstSearch.Selected(lstSearch.ListCount - 6500) = True
        Me!lstSearch.Selected(lstSearch.ListCount - 6500) = False
    Now it displays almost properly, but it's not scrolled all the way to the top; probably because I have a few more than 6500 records. I'll try to find a way to have it select the last record, not the 6500th, to fix that.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to trigger a button event at same form
    By newaccess in forum Forms
    Replies: 10
    Last Post: 03-01-2013, 10:02 AM
  2. Replies: 1
    Last Post: 12-18-2011, 01:52 AM
  3. Replies: 2
    Last Post: 08-22-2011, 09:02 PM
  4. Replies: 0
    Last Post: 03-11-2010, 02:11 AM
  5. On Load, form not visible
    By Bruce in forum Forms
    Replies: 15
    Last Post: 02-24-2010, 04:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums