Access create search Form

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.

Access provides a Form Function called Filter by Form which is very easy to use.

Select a Table/Query > Create > Form > Apply Filter/Sort

access_search_form_11

Now you can select multiple criteria, you can even select a value from dropdown box. However those values of dropdown boxes are independent of each other.

You can also use Or criteria by clicking the Or tab.

access_search_form_12

When you selection is done, click on Toggle Filter

access_search_form_13

A better way to do filtering is for users to choose from dependent dropdown boxes.

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 article will show you how to do such filtering.

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.

Query1
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

access_search_form

Now save the Form as result_frm. In case you would accidentally change the result data, you should change the Locked Property of each Text Box to Yes

access_search_form_04

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.

access_search_form_05

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

8 thoughts on “Access create search Form

  1. This is exactly what I’m looking for, however, whenever I run the form, the EmployeeID drop down is coming up as, “Enter Parameter Value” “Forms!search_frm!Combo_Dept”

    Could someone please help me solve this problem?
    Thanks

  2. Hi there. I am in desperate need of some help. I have created this exact form for my database, however, when I choose my “State” (employee ID), I am being prompted to type in whatever state it is as a parameter. Can anyone help me with this problem? My two drop downs are “Company” and “State.” Both are short text and once I bypass the parameter, it’s working great.

    1. Could someone explain how to remove the parameter?

    Also, when I choose a company name that is two words (has a space) I get an error message. Single word companies have no error.

    2. Could someone explain how to code for the space sometimes so I don’t have to take all of the spaces out?

    Thank you

    1. Hi John, prompting parameter means some fields in the Query that you refer to are broken, probably because the field name has been changed so Access fails to reference, but since the field is not critical Access still allows you to execute. Try to check the field name in each related Query and subquery.

      Where do you want to add space to?

      1. Thank you for your response. Everything matches identically to my Query. Any other ideas as to why it is bring up the parameter prompt? Is it because I have a text value instead of an autonumber?

        Thank you!

        1. If you use the wrong data type, you can still run the query. When you see the prompt, can you see which data field does it say that has issue

          1. Yes, it asking to type the particular state (for instance, VA or WV)… some of my “states” are numbers though like 1025.

          2. that means you have used a field call “state” in the form, but you don’t have the field in the data source (Table or query). The prompt doesn’t trigger because of the data type you use for the states, or whether your criteria is a text or number

Leave a Reply

Your email address will not be published.