How to Create a Search Form for Access Databases
How to create an adaptable search form that can be ported easily to other Microsoft® Access databases.
Last updated on 2024-04-29.
Requirements for a Search Form
In the majority of the databases that I've created for clients, their users want to be able to search for records according to a variety of search criteria.
So my design approach for a search form needs to follow these main principles:
- It must reflect the way in which most users go about making a search
- The search results must allow the user to move quickly to the record for which she's looking
- I want to be able to port the form to other databases with the minimum of fuss
- The form must dovetail in particular with other modules, e.g. the Person, Organisation and Party Data Model, that I use in the creation of a new database.
Search Form Design
Over the years I've gathered searches into a single template form. In a new database, I import the form and adapt it accordingly. This is a simple adaptation:
The template form has tabs for searches collected from many databases. For a new database, I dispense with those tabs that aren't appropriate, and benefit from ready-made VBA code attached to the buttons that populate the Search Results list.
The tab control is necessary only if all your search criteria won't fit on a single screen. If you've built your search template with a tab control, then it remains a useful way of delivering everything you need to a new database. You can always cut back the tab control, or even dispense with it altogether.
Preparing the Form
We'll focus on the search for a person:
When designing a form I apply names to the control objects as explained on the Conventions for Naming Objects in Microsoft Access Databases page.
The choice of names is intended to make as easy as possible the writing and understanding of the VBA attached to the on-click event of the command button named cmdPerson.
Let's consider names for the controls in the area of the form used for setting criteria for searching for people.
obPersonAND and obPersonOR are enclosed within the frame object fraPersonLogic. Note that the green frame enclosing the tick boxes, is a rectangular shape object not a frame object.
lbSearchResults is the name of the list box on the form into which cmdPerson delivers the results of a search.
Code to Perform the Search
This is VBA procedure that composes the criteria for the search and applies it to the names of people recorded in the table tblPerson, delivery the results to lbSearchResults:
Private Sub cmdPerson_Click()
On Error GoTo errHandler
Dim msg$, title$
Dim cptn$
Dim SQL$, where$
Dim fn%, ln%
title$ = "PEOPLE SEARCH"
' Commence construction of SELECT statement for list box's RowSource SQL
SQL$ = _
"SELECT PartyID, " & _
"[CurrentLastname] & chr(44) & chr(32) &[CurrentFirstname] " & _
"AS myPerson " & _
"FROM tblPerson "
' Commence construction of SQL WHERE statement
With Me
If .cbLastname Then
' User chooses to search for Last Name
' Check that user has input Last Name search string
If IsNull(.tebLastname) Then
msg$ = "Missing last name search string"
MsgBox msg$, vbExclamation, title$
.tebLastname.SetFocus
With .lbSearchResults
.RowSource = vbNullString
.Requery
End With
Exit Sub
Else
ln% = 1
End If
End If
If .cbFirstname Then
If IsNull(.tebFirstName) Then
' User chooses to search for First Name
' Check that user has input First Name search string
msg$ = "Missing first name search string"
MsgBox msg$, vbExclamation, title$
.tebFirstName.SetFocus
With .lbSearchResults
.RowSource = vbNullString
.Requery
End With
Exit Sub
Else
fn% = 2
End If
End If
' Continue construction of WHERE condition to include AND/OR logic
Select Case ln% + fn%
Case 1 'Lastname only - no logic component
cptn$ = "Results for Person's Last Name"
where$ = "[CurrentLastname] Like '*" & .tebLastname & "*'"
Case 2 'Firstname only - no logic component
cptn$ = "Results for Person's First Name"
where$ = "[CurrentFirstname] Like '*" & .tebFirstName & "*'"
Case 3 'Both Lastname and Firstname; apply logic
cptn$ = "Results for Person's Full Name"
' Determine logic and assimilate to complete WHERE condition
Select Case .fraPersonLogic
Case 1 AND
where$ = _
"[CurrentLastname] Like '*" & .tebLastname & "*'" & _
" AND " & _
"[CurrentFirstname] Like '*" & .tebFirstName & "*'"
Case 2 OR
where$ = _
"[CurrentLastname] Like '*" & .tebLastname & "*'" & _
" OR " & _
"[CurrentFirstname] Like '*" & .tebFirstName & "*'"
End Select
Case Else
msg$ = "Please make an appropriate choice of names"
MsgBox msg$, vbExclamation, title$
Exit Sub
End Select
' Complete SQL
SQL$ = SQL$ & " WHERE " & where$ & _
" ORDER BY CurrentLastname, CurrentFirstname;"
' Update list
With .lbSearchResults
.RowSource = SQL$
.Requery
End With
' Update list box caption
.lblSearchResults.Caption = cptn$
End With
procDone:
Exit Sub
errHandler:
msg$ = "Error performing search for people"
MsgBox msg$, vbExclamation, title$
Resume procDone
End Sub
The coding of the procedure isn't as elegant as it could be. I've presented it in this way in the hope that it makes for straightforward reading and for you to adapt to suit your own databases.
Telecomm, Email and Address Searches
The three sets of criteria for searches other than the one described in full for People, above, follow the same treatment.
I've run out of time to include guidance on these three, but if demand becomes high, I'll endeavour to publish an update to this article.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —