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.

Highlight functions in this file include:

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

Search by Keyword with Options of selecting Customer type for searching

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 1^st text box for all fields by checking all fields and all types of customers by leaving the combo box of customer type blank. The 2^ndkeyword will search from the result of the 1^st keyword or combining two keywords for searching.

(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 2^nd search text box will display and got focus to enter the 2^nd keyword. There are 6 records from the search result.

(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.

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

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

(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.

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

(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.

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:

(a) When I type “b” on the 1^st 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”.

(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”.

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

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