How to Create a Search Form by Keyword using VBA Part 1 (Related Video)
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
Luckily many of the levitra 40 mg robertrobb.com women find that they can choose the right one and make most of it. But the first two are just an auxiliary buy generic viagra method. Low testosterone level can affect a man’s desire to get intimate. http://robertrobb.com/category/state-and-local/ viagra properien It is for this reason http://robertrobb.com/do-we-really-have-to-litigate-over-wedding-invitations/ online cialis sale that it is made of pure herbs which pose no harm to the user.
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
Related Topic:
How to Create Search Form Using VBA Part 2
Create Report to view the search result on Search Form
Related Video Link:
Good post. I definitely appreciate this website. Thanks!
Keep this going please, great job!
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!
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!
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!
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
Amazing! Its truly awesome piece of writing, I have got much clear idea regarding from this article.
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!
great . helped me a lot… thanks so much!!
man thanks so much
It all worked well
Thanks a lot!!!
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…
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
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.
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
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.