Managing Long Lists in Microsoft Access

My recipe database comes with a few example recipes. What happens when the user adds multiple new recipes? Ten new ones would be very manageable. What about fifty? One hundred? Managing a long list of items can be made more accessible by these two things:

1. A search function to quickly narrow down the list

2. The ability to double-click any item to open its details instantly

The search function returns the matches for whatever the user enters in the search box. If a character string matching the search criteria is found in the recipe name, then the recipe appears in the list. This is true regardless of where in the name the characters appear – beginning, middle, or end.

Main Menu with a list of recipes.

Place an unbound text field atop your list for the user to utilize as their search box. In our case, the text field is named “txtFind.” In the “AfterUpdate” event of this text field, you’ll invoke the following procedure:

Private Sub txtFind_AfterUpdate()
'
' Filter the list depending on the input in the
' search box.
'
' Code by Jennifer Neighbors 
'

On Error GoTo ErrHandler

If Me.txtFind = Null Then
    Call Clear(Me.Form)
Else
    Call Find(Me.Form)
    Me.lstRecipes.SetFocus
    Me.lblClearSearch.Visible = True
End If

exitHere: Exit Sub
   
ErrHandler:

    MsgBox "Oops! It looks like there has been an error. " _
    & "The error is number " & Err.Number & ": " & _     
    Err.Description & " in " & VBE.ActiveCodePane.CodeModule & _ 
    & ". The program will now attempt to resume normally.", _ 
    vbOKOnly, "Error"
    Resume exitHere

End Sub

If the value in “txtFind” (the search box) has been emptied, it’s null, so clear the search and show all the recipes. To do so, call a procedure named “Clear.” If the search box does have a value, you’ll do three things: filter the list (show those recipes that match the search string), change the focus to the list box control containing the recipes, and unhide the X symbol at the right side of the search box. Here are the two public procedures we are working with: “Clear” and “Find.” I’ve placed these procedures in a general module (one that is not attached to any particular database object). This allows them to be called from any form in the database. Note that both procedures require a parameter indicating which form is calling. The parameter “me.Form” is sufficient. First, we have the Clear procedure:

Public   Sub Clear(frm As Form)
'
'   Clear the Find box and show all the recipes.
'   Code by Jennifer Neighbors
'

On Error GoTo ErrHandler

frm!txtFind = Null
frm!lstRecipes.RowSource = "SELECT " _
& "RecipeID, RecipeName FROM tblRecipes ORDER BY [RecipeName]"

exitHere: Exit Sub

ErrHandler:

    MsgBox "Oops! It looks like there has been an error. " _
    & "The error is number " & Err.Number & ": " & _     
    Err.Description & " in " & VBE.ActiveCodePane.CodeModule & _ 
    & ". The program will now attempt to resume normally.", _ 
    vbOKOnly, "Error"
    Resume exitHere

End Sub

And finally, here is the code for the Find procedure. This is where asterisks, or “wildcards,” are used in a SQL statement along with "double double" quotes.

Public Sub Find(frm As Form)
'
' Find the recipe the user is searching for.  There 
' could be more than one result. User "double double"
' quotes in the SQL statement in case a recipe name 
' has an apostrophe in it.
'
' Code by Jennifer Neighbors
'
Dim strSQL As String
Dim strLike As String
Dim strOrderBy As String

On Error GoTo ErrHandler

strSQL = "SELECT RecipeID, RecipeName FROM tblRecipes"
strOrderBy = " ORDER BY [RecipeName]"
strLike = " Where RecipeName like ""*" & frm!txtFind & "*"""

frm!lstRecipes.RowSource = strSQL & strLike & strOrderBy

'
' If there are no results from the search, let the user
' know that.
'
If frm!lstRecipes.ListCount = 0 Then MsgBox "Recipe not " _
& "found, you may try again."

exitHere: Exit Sub
   
ErrHandler:

    MsgBox "Oops! It looks like there has been an error. " _
    & "The error is number " & Err.Number & ": " & _     
    Err.Description & " in " & VBE.ActiveCodePane.CodeModule & _ 
    & ". The program will now attempt to resume normally.", _ 
    vbOKOnly, "Error"
    Resume exitHere

End Sub

Now, let’s look at the second of our two requirements when working with a long list: the ability to click any list item to open its details instantly. The user may select an item in the list and click the View/Edit button to open that item’s details. But there is a faster way: If a user encounters a lengthy list of items in a list box control, enabling the double-click feature to open the related detail record is also effective. This is an intuitive action that users are likely to attempt, and it can easily be implemented in your forms.

Adding this code to the list box’s “Double-click” event calls the code in the View/Edit button’s click event.

Private Sub lstRecipes_DblClick(Cancel As Integer) 
' 
' Open selected student's detail record. 
' Code by Jennifer Neighbors 
‘ 
 
On Error GoTo ErrHandler 
 
cmdViewEdit_Click 
 
exitHere: Exit Sub 
 
ErrHandler: 

cmdViewEdit_Click 
 
exitHere: Exit Sub 
 
ErrHandler:

    MsgBox "Oops! It looks like there has been an error. " _
    & "The error is number " & Err.Number & ": " & _     
    Err.Description & " in " & VBE.ActiveCodePane.CodeModule & _ 
    & ". The program will now attempt to resume normally.", _ 
    vbOKOnly, "Error"
    Resume exitHere
     
End Sub 

Here is the code being called to open the detail record:

Private Sub cmdViewEdit_Click() 
'
' Allow the user to navigate to the detail 
' form for the selected recipe. Make sure 
' a recipe is selected.
'
' Code by Jennifer Neighbors
'

On Error GoTo ErrHandler

If Not IsNull(Me.lstRecipes.Column(0)) Then
   DoCmd.OpenForm "frmRecipeDetail", acNormal, , _
    "RecipeID = " & Me.lstRecipes.Column(0), _
    acFormEdit
    DoCmd.Close acForm, "frmMainMenu"
Else
    MsgBox "Please select a recipe to view.", _ 
    vbOKOnly, "Information Required."
End If
 
exitHere: Exit Sub 
 
ErrHandler: 
 
    MsgBox "Oops! It looks like there has been an error. " _
    & "The error is number " & Err.Number & ": " & _     
    Err.Description & " in " & VBE.ActiveCodePane.CodeModule & _ 
    & ". The program will now attempt to resume normally.", _ 
    vbOKOnly, "Error"
    Resume exitHere
 
End Sub 

We’ve now looked at two methods that help to manage a long list when using Microsoft Access. What other techniques might be used? Do you have another suggestion for working with long lists? Let me know in the comments!