This Access tutorial explains how to create a search Form with multiple criteria.
You may also want to read:
Access DoCmd.OpenForm Method to open Access Form
Access Combo Box value depends on another Combo Box
Return Access Form input to Access Report or Form or Query
Access create search Form with multiple criteria
Sometimes you need to create a search Form where users can select specific criteria. The simplest solution is to directly filter data for each field like using AutoFilter in Excel, but this is not elegant at all. A better way to do this is for user to choose from dropdown box of different levels, value of one dropdown box depends on the value of another dropdown box.
For example, in the first dropdown box, users choose department A, and then in the second dropdown box, users can view a list of person of department A.
This tutorial will show you how to do this.
Create a result Form
Assume that we have a Query as below. Our goal is to allow users to select department to show employee information, or select department + employee ID.
We are going to make a “result Form” that shows the search result.
| Department | Employee ID | Employment Date | Salary | Allowance |
|---|---|---|---|---|
| Account | 1 | 1/1/2000 | 10000 | 100 |
| Admin | 2 | 1/1/2001 | 20000 | 200 |
| Admin | 3 | 1/1/2002 | 30000 | 300 |
| Admin | 4 | 1/1/2003 | 40000 | 400 |
| Payroll | 5 | 1/1/2004 | 50000 | 500 |
| HR | 6 | 1/1/2005 | 60000 | 600 |
Click on the Query and the under Create tab, select More Forms > DataSheet
Now save the Form as result_frm
In this example, I want to return all fields of Query1 in Result Form. I choose DataSheet because each record can be displayed in one row with horizontal scroll bar. I don’t prefer to use Continuous Form or Single Form because each record is displayed in several rows.
Create a Search Form
Create a blank Form and then add two Combo Boxes (Combo_Dept, Combo_EmpID) and one button (search_btn) as shown below.
1) Click on Combo_Dept, in the Row Source property, type
SELECT DISTINCT Department FROM Query1;
Row Source is what you can select in the ComboBox.
2) Click on Combo_EmpID, in the Row Source property, type
SELECT [Employee ID] FROM Query1 WHERE [Department]=[forms]![search_frm]![Combo_Dept];
3) Select Combo_Dept, select After Update Event and build the below Procedure
Private Sub Combo_Dept_AfterUpdate()
Me.Combo_EmpID = Null
Me.Combo_EmpID.Requery
End Sub
As you select a Department, Combo_EmpID is reset to Null and then rerun the query
4) Right Click on search_btn and then choose Build Event
Private Sub search_btn_Click()
If IsNull(Me.Combo_EmpID) Then
searchCriteria = "[Department]='" & Me.Combo_Dept & "'"
searchSQL = "Select * FROM Query1 where " & searchCriteria
Else
searchCriteria = "[Department]='" & Me.Combo_Dept & "' AND [Employee ID] =" & Me.Combo_EmpID
searchSQL = "Select * FROM Query1 where " & searchCriteria
End If
DoCmd.OpenForm "result_frm", 3, searchSQL
End Sub
Now you have successfully created a simple search Form. After you have selected criteria in Search Form, once you click the search button, the Result Form will pop up and displays the result.
Outbound References
https://msdn.microsoft.com/en-us/library/office/ff820845.aspx?f=255&MSPPError=-2147217396
Access create search Form
