Why Create a Microsoft Access Search Form?
There can be hundreds, thousands, or even millions of records in a database. Therefore, a search mechanism in a Microsoft Access database is a “must-have” item. For data integrity purpose, you may not want the users to query a table directly. in fact, it may be a good idea to hide the tables from users’ direct access. On the other hand, some users may not be sophisticated enough to know how to properly query data. it can be frustrating that users do not get exactly what they want. A better way is to use a form to help users do the query.
In addition to search the data, users may want to access a specific record of the searched result for editing or reporting. it will be handy that users can access to such record by simply clicking the listed queried records.
A combination of using a switchboard and some search forms will make all these happen. It will ensure your program is user-friendly and this is the key how receptive the users to your program.
Users may want to search an Access database in many different ways, whatever comes easy to them. Some may prefer searching by names. Others may search by Social Security Numbers, Account Numbers, Application Date, or Closing Date, etc. It is imperative if you can offer multiple ways of searching for data.
In the left illustration, I put diffferent search options for users. The design of each search form is essentially from the same report template. There is a search box (or multiple search boxes) on top and a subreport at the bottom. After I created the first search form, it becomes the template for other search forms.
Let’s use the “Search Name” form as the first search form. After creating this form, it will become the template of all other search forms. Click “Create” on the top line menu (ribbon) and then select “Blank Form”.
Use the “Form Design Tools” to create Text Boxes, Subforms, and Buttons.
The data source of the Subform is from the “Customer” Table. There are two ways that you can access the data by the subform. First way is to directly use the “Customer” table as data source. The second way is to create another Subform which shows the data in a tablet format. The advantage of creating another subform is that you can have some control on the size (column width) of the data that shows in the Subform. This is particularly useful if you try to fit the data into a limited width. However this is not a necessity. The Subform that I created is named as “CustomerList”.
Each object on the Access form will have its own properties. The properties will show up on the “Property Sheet” which appears on the right hand side of screen in the Form Design mode. If you do not see the “Property Sheet”, please click the “Property Sheet” icon when you are at the “Access Design Tool”=> “Design” menu (ribbon).
In the Property Sheet of the Subform, I put in “CustomerList” (another Subform) as the Source Object. Then I named the “Name” as “SearchSubForm”. The naming is handy when it comes to editing the event procedure (a Visual Basic Application).
I have two text boxes in this form. One is for First Name, and the other is for Last Name. In the Property Sheet of the Text Box, I named them as “FirstNameTB” and “LastNameTB” respectively where TB is the abbreviation of “Text Box”.
Let’s work on the “FirstNameTB”. Click the “Event” tab on the “Property List”, choose an [Event Procedure] on the “After Update” event by clicking the down-arrow button. Then click the “…” button and the Visual Basic Editor will come up. Put in the following VB codes.
Private Sub FirstNameTB_AfterUpdate()
Dim FirstNameChoice As String
FirstNameChoice = “Select * from Customer where ([FirstName] Like ‘” & Me.FirstNameTB & “*’ AND [LastName] like ‘” & Me.LastNameTB & “*’) order by FirstName, LastName”
Me.SearchSubForm.Form.RecordSource = FirstNameChoice
Me.SearchSubForm.Form.Requery
End Sub
Repeat the same process for LastName text box. Use similar VB codes for LastNameTB as well. Please see below.
Private Sub LastNameTB_AfterUpdate()
Dim LastNameChoice As String
LastNameChoice = “Select * from Customer where ([FirstName] Like ‘” & Me.FirstNameTB & “*’ AND [LastName] like ‘” & Me.LastNameTB & “*’) order by FirstName, LastName”
Me.SearchSubForm.Form.RecordSource = LastNameChoice
Me.SearchSubForm.Form.Requery
End Sub
The VB codes will exercise every time when a user types in words in the First Name Text Box and/or Last Name Text Box and hit the “Enter” or “Tab” key. The list of records in the subform “SearchSubForm” will instantly display the search result due to the use of “Requery” function in Visual Basic. The user can type in the full first name (such as “Carlos”) to see a list of customers with the name of “Carlos” or just type in “C” to see a list of customers with first name started with “C” that will include “Carlos” in it.
Please note that when we use the syntax “Like” in the SQL statement in the VB codes, it will pull up both capitalized and non-capitalized letter. In addition, the use of “*” in the SQL statement will help pull the data even though the input is partial.
After the user gets the search list, I want the user to be able to access a particular record by just clicking such record. In addition, I want that the user can click any field of such record to access it (to make it more user-friendly, it is not a necessity though). Therefore, we need to put an event procedure to each field of the subreport.
First click a field on the subreport, then choose an “Event Procedure” in the “On Click” field. Click the “…” button to access the Visual Basic Editor. Put in the below VB codes for such event.
Private Sub ID_Click()
Dim GoRecord As String
GoRecord = “ID = ” & Me!ID
DoCmd.OpenForm “InputForm”, , , GoRecord
End Sub
The “InputForm” mentioned in the above VB codes is a simple form that can edit all the fields that related to a record in the Customer table. The record is identified by the ID field of such record.
Repeat the process for other fields in the subreport. For example, put in the below VB codes for the second field (First Name)
Private Sub FirstName_Click()
Dim GoRecord As String
GoRecord = “ID = ” & Me!ID
DoCmd.OpenForm “InputForm”, , , GoRecord
End Sub
When it is all done, it will look like below. Please note that all data are faked.
I typed “J” in the First Name Text Box and all customers with the first name started with “J” will come out. Please note that I typed in a non-capitalized “j” but the LIKE keyword in the SQL statement will pull records of both capitalized and non-capitalized letters.
Now 4 records show up and the user can click any field of a particular record and edit the data of such record with all the fields.
To make it more user-friendly, you may add buttons such as “Clear Filter” or “Close Form”. The button can be found on the “Form Design Tools”. Microsoft Access has included a “Close Form” function that you can choose when creating a button, so there is no need to write an “Event Procedure”. For the “Clear Filter” function, you need to create your own “Event Procedure” for “On Click” event. All you need to do is to set the data in any text box to be “Null” and then perform the “Requery” function. Below is the VB Codes.
Private Sub AcctNumTB_AfterUpdate()
Dim AcctNumChoice As String
AcctNumChoice = “Select * from Customer where [AccountNumber] Like ‘” & Me.AcctNumTB & “*’ order by AccountNumber”
Me.SearchSubForm.Form.RecordSource = AcctNumChoice
Me.SearchSubForm.Form.Requery
End Sub
After you create the first search form, you can use it as a template for another search form. Please keep the subform as it is. You only need to change the input text box. For example, you can create another search form by searching the Account Number. The VB code will be as follow..
Private Sub ClearNameFilter_Click()
Dim NameChoice As String
Me.FirstNameTB = Null
Me.LastNameTB = Null
Forms!SearchName!FirstNameTB.SetFocus
NameChoice = “Select * from Customer order by FirstName, LastName”
Me.SearchSubForm.Form.RecordSource = NameChoice
Me.SearchSubForm.Form.Requery
End Sub
It is a good idea to insert one more “Event Procedure” upon the loading of the search form to make sure that every time when the search form is loaded, the previous searched input will not be carried over during loading. The “Event Procedure” will be almost identical to the above VB codes and needed to be put in the “On Load” event when the entire search form is loaded.
Private Sub Form_Load()
Dim NameChoice As String
Me.FirstNameTB = Null
Me.LastNameTB = Null
Forms!SearchName!FirstNameTB.SetFocus
NameChoice = “Select * from Customer order by FirstName, LastName”
Me.SearchSubForm.Form.RecordSource = NameChoice
Me.SearchSubForm.Form.Requery
End Sub
