Create Search Form using VBA : MS Access

How to Create a Search Form by Keyword using VBA Part 1 (Related Video)

downloadbutton

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.

KeywordSearch

Create Search Form Step-by-Step

1. Create a blank form

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

RecordSource

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.

txtSearch

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

FormView

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

RecordSource

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

ShowAll

 

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_OR

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_AND

 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

Search_Date

Related Topic:

How to Create Search Form Using VBA Part 2
Create Report to view the search result on Search Form

 Related Video Link:

Related posts

15 Thoughts to “Create Search Form using VBA : MS Access”

  1. Good post. I definitely appreciate this website. Thanks!

  2. Keep this going please, great job!

  3. Hi there! This is my first comment here so I just wanted to give a quick shout out and tell you I really enjoy
    reading your posts. Can you recommend any other blogs/websites/forums that deal with the same subjects?
    Appreciate it!

  4. This design is spectacular! You certainly know how to keep a reader entertained.
    Between your wit and your videos, I was almost moved to start my
    own blog (well, almost…HaHa!) Wonderful job. I really loved what
    you had to say, and more than that, how you presented it. Too cool!

  5. fantastic publish, very informative. I wonder
    why the other experts of this sector do not understand this.
    You should continue your writing. I’m sure, you’ve a great
    readers’ base already!

  6. I delight in, cause I discovered just what I used to be looking
    for. You’ve ended my 4 day lengthy hunt! God Bless you man. Have a nice day.

    Bye

  7. Amazing! Its truly awesome piece of writing, I have got much clear idea regarding from this article.

  8. Thanks on your marvelus posting!I certainly enjoyed reading it, you’re a great author.

    I will make surre to bookmark your blog and will eventually come back from noow on. I
    want too encourage yourself to continue your great posts, have a
    nice day!

  9. MonMon

    great . helped me a lot… thanks so much!!

  10. victor

    man thanks so much
    It all worked well
    Thanks a lot!!!

  11. mohammad hanif

    thank you for your videos and all stuff, but i am in deep need of your help in some coding procedures.
    i am developing a program in MS Access 2010 for my premises.
    but when i am using your codes, i am getting error messages telling about the syntax error in codes.
    please stay in touch to help me…

  12. Raj

    Hi Austin,

    I am watching your videos for quite some time and really the videos are very informative and easily understandable. Currently I am struct in one instance where If i had customer database and in that database the customer taking the help from another 2 customers . How I can create separate database for each of those 2 customers who are doing the same job as my original customer and create a database simultaneously from the original database.( Please note only the customer is changing rest all the entries are same as original customer. Please help me to create a form for the same.
    Raj

    1. TGoldenEye

      can we use one database which has a customer table, job table and workprocess table? Many customers from customer can perform same job id from a job table in different workprocess id and date on the workprocess table.

  13. Mohammed Hashim

    Thanks austin

    I have done about the date(specific)

    one more thing to help me how to search one date to other date

    ex : 12/04/2013 to 10/04/2014 enter date to two different coloms.

    I am beginner in access through youtube . sorry to bother you.
    Thanks for the help for the search coloum

    1. TGoldenEye

      you need to use the query to put the criteria, for instance you have StartDate field and EndDate field.
      you can put criteria on query as >[enter start date] on StartDate field and <[Enter end date] on the EndDate field. I haven't have a video on how to work with query.

Comments are closed.