Changing List Box Results in MS Access

Our publications database currently has about 81,000 items listed in it, mostly union and company grey literature. The database opens to a search form which contains a list box. This used to load with all the items when opened, but once we got past 60,000 it was slow to load. Since there is no need for all items to load I looked for ways to limit the list box results. There is probably a way to load 100 random records, but I wasn’t able to figure one out so instead it started with the first 100 records and each time the database is opened or the form refreshed it opens the next 100 in sequence.

To control where the record set begins I added a new table to the database, tblLoad. There are only two fields and one record in the table. The ID field has a value of 1 and never changes, while the Start field contains the lower limit for the list box record set and is initially had a value of 1 but the value is increased by 99 whenever the search form opens or is refreshed.

Here is the VBA code for the search form’s On Open event:

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False

     Dim strSQL As String           'This is the SQL query that provides the record set for the list box.

     Dim lngStart As Long           'This establishes the lower limit of the record set.

     Dim lngEnd As Long            'This establishes the upper limit of the record set.

     Dim lngMax As Long           'This is the ID for the last record in tblPams.

     'Pick up the lower limit from tblLoad.

     lngStart = DLookup("[Start]", "tblLoad", "[ID] = 1")

     'Debug.Print "lngStart = " & lngStart

     'Create the upper limit by adding 99 to the lower limit.

     lngEnd = lngStart + 99

     'Debug.Print "lngEnd = " & lngEnd

     'Pick up the ID for the last record in tblPams.

     lngMax = DMax("[PamID]", "tblPams")

     'Debug.Print "lngMax = " & lngMax

     'To avoid generating a error when the SQL query is run

     'test to make sure that lngEnd is not greater than lngMax.

     If lngEnd > lngMax Then

     'If lngEnd is greater than lngMax reset lngEnd to lngMax.

          lngEnd = lngMax

     Else:

     'Otherwise, keep lngEnd the same.

          lngEnd = lngEnd

     End If

     strSQL = "SELECT tblPams.PamID, tblPams.Title, tblPams.Date FROM tblPams "

     strSQL = strSQL & "WHERE (tblPams.PamID BETWEEN " & lngStart & " AND " & lngEnd & ") "

     strSQL = strSQL & "ORDER BY Article(tblPams.Title), tblPams.Date;"

     'Debug.Print strSQL

     Me.lstPamList.RowSource = strSQL

     'If the last record in the table has been reached we need to reset the lower limit to 1.

     If lngEnd = lngMax Then

          DoCmd.RunSQL "Update tblLoad SET tblLoad.Start = 1 Where tblLoad.ID = 1"

     Else:

     'Otherwise, the current upper limit becomes the next lower limit.

          DoCmd.RunSQL "UPDATE tblLoad SET tblLoad.Start = " & lngEnd & " Where tblLoad.ID = 1"

     End If

DoCmd.SetWarnings True

End Sub