Home Page     Contact Dave Thompson Consulting    Site Map

Dave Thompson Consulting
408 Rose Drive
    Allen, Texas 75002   
(Dallas/Ft Worth area)
(972) 727-5670

A List Navigation and Drill Down User Interface for MS Access

Building the List Form

Section 3 -  Find Commands


Section 1 - Data/Form

Section 2 - Underline Data

Section 3 - Find Commands

Section 4 - Drill Down

Client database example is described in more detail on this site (See Client Information System - Introduction)

While the underline and move feature can be used to navigate through the data list, for large lists this is not an efficient way to find the desired record.

Adding a search command for the most used data fields will allow the user to quickly locate a specific record.

Fig. 3-1 Search Command Buttons

We will provide search commands for:


Client Number


Last Name









Some considerations for the functionality of a search command are:


Choosing a field (column) for searching, should also sort the form by that field.


For text fields (such as Last Name) allow the user to enter only a few leading characters.


When sorting, consider secondary fields such as Last Name then First Name.


Use VBA procedures for common tasks and to avoid unnecessary sort operations.

(Click to enlarge. Opens in new window.)
Fig. 3-2 Search for Client Number - On Click Event


Private Sub FindNumberCMD_Click()
Dim fx As String
Dim c As Control

On Error Resume Next

If Not HasRecords() Then ' procedure to confirm that the form has one or more records displayed
    Exit Sub
End If

fx = InputBox("Please enter the full client number" & vbCrLf & "(for example: 1001):", "Find by Client Number", "")

If fx = "" Then ' the user entered nothing
    Exit Sub
End If

If mLngFormSort <> mLngByNumber Then ' if the form is not sorted by client number, then do the sort
    FormSort mLngByNumber ' a procedure used by every search command
End If

Set c = ClientNumber

    c.Enabled = True ' control was set in design view for Enabled = No (False)
    c.SetFocus ' if we do not enable the control, it cannot have the focus

    DoCmd.FindRecord fx                     

    Dummy.SetFocus ' move the focus off ClientNumber before next program line
    c.Enabled = False  'must remove focus from control (line above) before setting
                       'Enabled = False     

If ClientNumber <> fx Then ' did we find the search Client Number?
    MsgBox "Client Number " & fx & " not found.", vbInformation, ""
End If

End Sub


The procedure above, Sub FindNumberCMD_Click(), uses two module level procedures:



Fig. 3-3 Module Code Button on Menu Bar

Code Module Declarations (Click to enlarge)
Fig. 3-4 Code Declarations

These declarations are in the General section of the form's code module. They are local to this one form.

Module level code procedures (Click to enlarge)
Fig. 3-4 List of code procedures

FormSort - Used to sort the form.

GoToPCForm - Drill down to full client record.

HasRecords - Check that the form has records displayed.

RequeryAndFind - Call if underlying data changes, or if a new record is added.


Private Function HasRecords() As Boolean

On Error GoTo TrapIT
Dummy.SetFocus 'if has no records, this will cause an error
HasRecords = True 'we get here if form has one or more records

Exit Function
HasRecords = False ' set to false on error
Resume EnterHere

End Function


Private Sub FormSort(y As Long)
On Error Resume Next 'run under all conditions

If Not HasRecords() Then
    Exit Sub ' nothing to do
End If

Me.OrderByOn = True

mLngFormSort = y

Select Case mLngFormSort

    Case mLngByNumber
        Me.OrderBy = "[ClientNumber]"

    Case mLngByName
        Me.OrderBy = "[LastName], [FirstNameMI], [ClientNumber]"

    Case mLngByCity
        Me.OrderBy = "[City],[State], [LastName], [FirstNameMI], [ClientNumber]"

    Case mLngByState
        Me.OrderBy = "[State], [City], [LastName], [FirstNameMI], [ClientNumber]"

    Case mLngByZip
        Me.OrderBy = "[Zip], [LastName], [FirstNameMI], [ClientNumber]"

    Case mLngByPlanner
        Me.OrderBy = "[PlannerName], [LastName], [FirstNameMI], [ClientNumber]"

End Select

End Sub


Search by City (Click to enlarge)
Fig. 3-5 Search by City Example


Private Sub FindCityCmd_Click()
Dim fx As String
Dim c As Control

On Error Resume Next

If Not HasRecords() Then
    Exit Sub
End If

fx = InputBox("Please enter first few characters of City:", "Find by City", "")

If fx = "" Then
    Exit Sub
End If

If mLngFormSort <> mLngByCity Then ' sort only if needed
    FormSort mLngByCity
End If

Set c = City
    c.Enabled = True
    DoCmd.FindRecord fx, acStart 'search leading characters in field
    c.Enabled = False

If Left(City, Len(fx)) <> fx Then
    MsgBox "City beginning " & fx & " not found.", vbInformation, ""
End If

End Sub