DMW logo
tel 01732 833085
tonbridge · kent · UK

How to Create a Search Form for Access Databases

How to create an adaptable search form that can be ported easily to other Access databases.

Last updated on 2020-09-30 by David Wallis.


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 varied search criteria. So my design approach for a search form needs to follow these main principles:


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:

Access search form

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.

Search form pages

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:

People search screen

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.

People search screen

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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification

“I’m always interested in something that’s missing.”

Clive Cussler