Building the List Form

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

Introduction

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
	City
	State
	ZIP
	Planner

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:

HasRecords
FormSort

Fig. 3-3 Module Code Button on Menu Bar

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.

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
EnterHere:

Exit Function
TrapIT:
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

'----------------------------------------------------------

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
    c.SetFocus
    DoCmd.FindRecord fx, acStart 'search leading characters in field
    Dummy.SetFocus
    c.Enabled = False

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

End Sub

'----------------------------------------------------------