Advance Search by Keyword for MS Access

Download Sample Access File: Advance Search by Keyword for Access

(for Version 2010 and up)

This sample Search Form contains 4 different types of searching with different ways of coding in VBA, from the simple way to the difficult way.

nav form

Highlight functions in this file include:

  1. Search by Keyword – Search function by Keyword using SQL statement in VBA for a record source for form

search by keyword

  1. Search by Keyword with Options of selecting Customer type for searching

searchpart2

3. Advance Search – (Video Link)The Advance Search form provides two search boxes for keyword entry. The search result will come from the combination of three main groups: two search keywords, check boxes for fields information to search, and combo box for the type of customers. You can search one keyword from the 1st text box for all fields by checking all fields and all types of customers by leaving the combo box of customer type blank. The 2nd keyword will search from the result of the 1st keyword or combining two keywords for searching.

open advance search

(a) The example below is searching keyword “ma” for all fields and all types of customers. After typing “ma” and hit enter key then the 2nd search text box will display and got focus to enter the 2nd  keyword. There are 6 records from the search result.

advance search

(b) The example below is searching keyword “Ma” for customer name only (checking only Name) and all types of customers. There will be only 4 records from the search result. The Search Function is put under all actions; after typing keyword and hit enter key, check /uncheck field names, click Select All/Select None button, click clear button(x), or select customer type.

keyword and field

(c) The example below is searching keyword “Ma” and “Joh” on the 2nd keyword for all fields and all types of customers. There is only one record from the search result.

search two keywords

(d) You can also view or print the report of the result from searching by clicking on Preview button after entering keywords.

search two keywords view

 (e) The example below is searching keyword “Ma” for all field names and selecting the “Non-Profit” for customer type. There will be narrowed down to only 2 records from the search result.

keyword and type

(f) You can add new customer by clicking on New Customer button.

add new

(g) The command for Show or Hide a column name on the datasheet is included in this form. You can show only the column you want to see the information on this form.

show and hilde field

 4. Search As Typing – (Video Link) this searching function is another advance search for MS Access. This Search As Typing form has all functions same as the advance search form mentioned above, but only the difference is this Search As Typing form displays the result of searching while you are typing on each keystroke, you don’t have to press Enter. The Advance Search form will display the result after you hit Enter key or click another key to update the result like selecting customer type. I will show the result of using the Search As Typing below:

search as typing

(a) When I type “b” on the 1st keyword box the datasheet will automatically display the list of customers as the result from searching. The user doesn’t have to press Enter key after typing “b”. There are 13 records found from the typing “b”.

typing b

(b) Then I type “a” after “b” as shown “ba” on the search box, the datasheet will automatically update the list of customers as 2 records found from searching for “ba”.

type ba

(c) Now I type the 3rd letter as “k” total as “bak” the datasheet will update and shows only one record as a resulting of typing “bak”.

type bak

(d) I can use keywords on both search boxes. After I type “b” on the 1st box then type “559” on the 2nd box the datasheet will display 2 records matching.

two keys

  1. Email customer via MS Outlook.

emailemail via outlook

  1. Set security level for user type. The Admin tab will be disabled if user logged in as a regular user.

guest login

Download file contains:

4 tables, 15 Forms, 1 Report, and 2 Modules

form  table  module