Download Sample Access File: Search Multiple Fields
Create Search Form for multiple items in multiple fields using different search methods. There are five forms in this sample file. Each form shows different type of search function or filtering multiple data items by using text box, combo box and/or list box. This sample file also shows how the text boxes, combo boxes and list box works together.
Highlight functions or features in this sample file include:
Form 1 – Search Multiple Fields
It is considered a complete search form since it is able to search on multiple fields with different methods. It uses a combination of text box, list box and combo box. There are different types of input data for searching listed below:
#1 You can search by key words on Customer Name, City, and phone fields. The example below is searching for “los” on City field and “Male” on Gender field. The result of male gender and live in Los Angeles city are displayed below.
#2 Search multiple number or zip codes in one text box by using comma (,) as a separator. The result will display all records with zip code that match the input zip codes on Zip Code field.
#3 Search the date range on the Date field by entering the starting date and the ending date. On the example, entering date from 1/1/15 to 10/1/15 on the DOB field it will display all customers that born from 1/1/15 – 10/1/15.
#4 Search the number range on the number field by entering the starting number and the ending number. For example, entering number from 2 to number 10 on the Age field it will display all customers that have age between 2 to 10.
#5 This form also provides a Preview Report button to print the result from searching. It also provides a Clear All button to clear all inputs and Clear Select button to clear the select check box on the list.
#6 Print certain record after search. If you want to print only certain record from the list you can check box on the select column then click Preview Report button. If there is no check on any record it will print all records on the list. For example, check on record of Sven Mortensen and Roland Wacker then click Preview Report it will display only two records of Sven Mortensen and Roland Wacker as shown below:
Form 2 – Search Three Fields Using a Combo Box and List Box
This part is combining a combo box or list box for searching or filtering on multiple fields. There are three fields used in this sample file. The result of searching will display on this split form if all criteria matching on three fields. If there is no item selected on the combo box or list box it will be no criteria for that field and will display all data for that field.
#1 Search or filter on multiple fields in combination of selecting a single item from a combo box and multiple items on a list box. Example below:
Gender = Male
State = CA, Co or NV
Customer Type = Individual
#2 This form also provides a button to preview a report or list of customer that is a result from the search or filtered.
#3 Clear Combo box and List box. A Clear List button will clear all combo box and list box for Gender, State and Customer Field. Then it will set a filter to false and display all data on this split form.
Form 3 – Search Form for Multiple Items Using a Combo Box and List Box
This part is a combination of a combo box or list box for searching or filtering. Users have an option to select a single item by using a combo box to filter or can able to select multiple items by using a list box.
#1 Search multiple items by selecting a –Select Multiple- item from a combo box or by clicking on the (+) button to display a list box. Then user can select multiple items from the list box.
For Example:
Customer Type = Government or Individual
#2 The combo box will display the item as –Select Multiple- is selected after clicking (-) button to hide the list box. The value of multiple selected items is stored in a temp variable and references to the Customer_Type_ID of –Select Multiple- item.
#3 Clear Combo box and List box. A Clear List button is provided to clear the selected item from the combo box and list box. It will set a filter to false and display all data from a Customer table on this split form.
Form 4 – Search Using a List Box for a Number or Integer
After the item is selected on the list box it will call a search function/sub to filter the customer type and display on the datasheet of a split form.
A Row Source of the list box comes from a table Customer Type. Two fields are listed on the query of row source. The list box references to the first column on the query which is a Customer_Type_ID field. This field has a data type as integer or number. The search function is looking for the number of Customer type ID on the list box. So the VB code is different from the previous search for State list box. You will find it in this form.
This form also provides a button to preview a report or list of customer that is a result from the search or filtered.
Form 5 – Search Using a List Box for Text or String
After the item is selected on the list box it will call a search function to filter the states and display on the datasheet of a split form. The state of CO, NV and TX is selected on this example.
A Row Source of the list box comes from a group of data from a State field of Customer table. A list of data or State will depend on the input data on this table. This field has a data type as text or string.
This form also provides a button to preview a report or list of customer that is a result from the search or filtered.
Modules
There are two modules that contain some public functions which are used in the VB in forms.
For example, public function ClearList() is used to clear the items in list box. This function is called when the Clear List button is clicked.
Download file contains:
2 tables, 1 Query, 5 Forms, 1 Report and 2 Modules
