Create Search Item Using VBA

MS Access 2010 has a built-in text filter function on the datasheet form; however, it is still attached to the individual field. It works only on the datasheet form. We can search on a single form by using the VBA function to search for data on one field or more fields that we want to search for.

Create Search Form Step-by-Step

1. Create a blank form

2. Under the property sheet, select table tbl_Customer as a Record Source

3. Under the design view, add Customer_id, CustomerName, Address, City fields etc. in the detail section of the form. Insert a vertical line to separate each field and a horizontal line to separate record.

4. Insert a textbox and name it as txtSearch for a keyword search on the Form Header section

5. Insert two buttons for Search and Show all button on the Form Header section

6. Under the property sheet, select a “Continuous Forms” for a Default View

7. Under the property sheet, select a “Dynaset” for a Recordset Type

8. Load a blank form on Open: We will load a form with no record from a table customer by using the SQL statement with the criteria of Customer_id is null. Normally, all customers have the customer id in this form because the customer_id is a primary key.

Private Sub Form_Load() Dim Task As String ‘load form with a yellow color background on a search box Me.txtSearch.BackColor = vbYellow Task = "SELECT * FROM tbl_customer WHERE (customer_ID)is null" Me.RecordSource = Task Me.txtSearch.SetFocus End Sub

9. Show All records from table customer: on the Show All button, put code below under the On Click Event Procedure on the property sheet.

Private Sub Command94_Click() Dim Task As String Task = “SELECT * FROM tbl_Customer” Me.RecordSource = Task End Sub

10. Search keyword from the textbox txtSearch: on the Search button, put code below under the On Click Event Procedure on the property sheet. The example below is searching for a Customer Name using Like “*”… keyword….”*”

Search on a single field

Private Sub Command163_Click() Dim strsearch As String Dim Task As String 'Check if a keyword entered or not If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed" Me.txtSearch.BackColor = vbYellow Me.txtSearch.SetFocus Else strsearch = Me.txtSearch.Value Task = "SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*""))" Me.RecordSource = Task Me.txtSearch.BackColor = vbWhite End If End Sub

Search data from multiple fields using operator “OR”

The result of using OR operator between fields will be wider than single field because all data that matching the search keyword from multiple fields will show on the result. For instance, searching keyword for “Fresno” on three fields: “CustomerName, City or Address” the result will show as follow:

strsearch = Me.txtSearch.Value Task = "SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*"") OR (city Like ""*" & strsearch & "*"") OR (Address Like ""*" & strsearch & "*""))" Me.RecordSource = Task

Search data from multiple fields using operator “AND”

The result of using AND operator between fields will be narrow than single field. The result of searching must have a data matching the search Keyword on all search fields. For instance, searching keyword for “Fresno” on three fields: “CustomerName, City and Address” the result will be one record that has “Fresno” on all three fields as shown below:

strsearch = Me.txtSearch.Value Task = "SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*"") AND (city Like ""*" & strsearch & "*"") AND (Address Like ""*" & strsearch & "*""))" Me.RecordSource = Task

Search for a specific date on a date field

The data type of search textbox of txtSearch is text. When we want to include a date field in the search keyword we have to change a data type of search textbox to a date format and test if it’s date data type entered by IsDate function, otherwise get an error when a text data entered. We will use a SearchDate = Format(Me.txtSearch.value, shortdate) for a date field. For instance, search for the anniversary date 4/28/14:

Dim SearchDate As Date If IsDate(Me.txtSearch) Then SearchDate = Format(Me.txtSearch.Value, Shortdate) Task = "SELECT * FROM tbl_customer WHERE ((Anniversarydate Like ""*" & SearchDate & "*""))" Me.RecordSource = Task Me.txtSearch.BackColor = vbWhite Else strsearch = Me.txtSearch.Value Task = "SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*"")OR (Anniversarydate Like ""*" & SearchDate & "*""))" Me.RecordSource = Task Me.txtSearch.BackColor = vbWhite End If