How to Build a Simple Search Form in Access

You can make a search form in Access by adding input controls. These can be text boxes or combo boxes. These controls let you type search words and filter data. You can use VBA or queries to do this. Many beginners have trouble with database design. They also struggle with adding fields and using design controls. The table below lists some common problems you may see when you build search form features:

You do not need advanced skills to begin. Access lets beginners and intermediate users make helpful forms with easy steps.

Key Takeaways

• Begin your search form by making or opening a form. Add input controls like text boxes and combo boxes. These let users type in search words.

• Use combo boxes to help users search faster. Combo boxes let users pick from lists. This helps stop typing mistakes.

• Put VBA code in your form to filter data as users type. This makes searches more flexible and quick.

• Make your search form better by adding more search fields. Link tables so you can find records with many rules at once.

• Add clear and reset buttons to your form. These let users erase search words and filters fast. This keeps the form simple and free of mistakes.

Build Search Form Basics

Create or Open a Form

To start, you need to create or open a form in Access. You can use an existing table or query as the data source.

1. In the Navigation Pane, select your table or query.

2. Go to the Create tab and choose Form. Access will open a new form in Layout view.

3. If you want a blank form, select Blank Form from the Create tab.

4. Use the Field List pane to drag fields onto your form.

5. You can also add headers or footers for titles and logos.

Tip: Give your form a clear name and set a meaningful caption. This helps you find it later and keeps your database organized.

Add Search Controls

You need to add controls so users can enter search criteria. The most common controls are text boxes and check boxes.

• Text boxes let users type in search words, like a name or ID.

• Check boxes allow users to include or exclude certain fields in the search.

• Add a command button to start the search.

• You can also use a list box to show search results.

Place labels next to each control. Put labels above or to the left of the input area. This makes the form easy to read. Stack fields vertically and group related fields together. Adjust the size of each field to match the expected input. Use white space to keep the form comfortable and clear.

Note: Input masks help users enter data in the right format, such as phone numbers. This keeps your search accurate.

Use Combo Boxes

Combo boxes make your search form more user-friendly. They let users pick from a list or type their own value. Combo boxes help prevent mistakes and speed up data entry. When users start typing, the list filters to show matching items. This feature makes it easy to find records quickly.

You can set up combo boxes to allow new entries if the needed value is not in the list. This saves time and keeps your data up to date. Combo boxes also work well for fields with many choices, like categories or locations.

Tip: Use drop-down menus for fields with many options. This keeps your form neat and helps users make the right choice.

By following these steps, you can Build Search Form features that are simple and effective. Use clear labels, logical grouping, and the right controls to make your form easy for everyone.

Filter Data with VBA

Adding VBA to your search form lets you filter data based on what users type or select. VBA gives you more control and flexibility than using queries alone. You can make your form respond instantly to user input and create a smoother experience.

Access VBA Editor

You need to open the VBA editor before you can add code to your form. Follow these steps:

1. Open your form in Design View.

2. If you do not see the property sheet, press F4 to show it.

3. Go to the Event tab in the property sheet.

4. Click in the event property box (like On Click) that shows [Event Procedure].

5. Click the build button (the three dots) next to the event property box.

Access will open the Visual Basic Editor. You can also press Alt + F11 to open the VBA editor directly after opening your database.

Tip: The VBA editor is where you write and edit code for your form. You can add code to buttons, text boxes, or the form itself.

Write Filter Code

VBA lets you filter records based on what users enter in your search controls. You can use unbound text boxes or combo boxes to collect search terms. Then, you build a filter string and apply it to the form.

Some advantages of using VBA for filtering include:

• Easier debugging and more flexibility than parameter queries.

• Dynamic control, such as linking subforms to combo boxes for instant filtering.

• Support for user interface features that are hard to do with queries alone.

You often use the Form.Filter and Form.FilterOn properties to apply filters. For example, you can filter records based on a text box input. Here is a sample code block that filters records by a name entered in a text box called txtNameFilter:

Example VBA Code:

On Error GoTo errHandler

Dim filterText As String

'Apply or update filter based on user input.
If Len(txtNameFilter.Text) > 0 Then
    filterText = txtNameFilter.Text
    Me.Form.Filter = "[CA_StatTimeSearch]![LookupTxtBox] LIKE '*" & filterText & "*'"
    Me.FilterOn = True
    'Retain filter text in search box after refresh.
    txtNameFilter.Text = filterText
    txtNameFilter.SelStart = Len(txtNameFilter.Text)
Else
    'Remove filter.
    Me.Filter = ""
    Me.FilterOn = False
    txtNameFilter.SetFocus
End If

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub

This code checks if the text box has any text. If it does, the code filters the form to show only records that match the text. If the box is empty, the filter is removed. You can use similar code for other fields, such as dates or numbers. For text fields, use the LIKE operator for partial matches. For date fields, use exact matches.

You can also use the DoCmd.OpenForm method with a wherecondition argument to filter records when opening a form or report. This method is simple and works well for single-criteria searches.

Attach Code to Button

You need a way for users to run the filter code. The most common way is to add a command button to your form. You can attach your VBA code to the button's Click event. Here is how you do it:

1. Insert a command button on your form in Design View.

2. Place the button where users can see it easily.

3. Open the VBA editor and select the button's Click event.

4. Write or paste your filter code into the Click event subprocedure.

5. Close the VBA editor and return to Form View.

6. Test the button by entering a search term and clicking the button.

When you click the button, Access runs the VBA code and filters the records. You can always go back to Design View to change the button or update the code.

Note: VBA filtering works best when your database is stored in Access. If you use a remote server like SQL Server, queries may run faster because they only fetch filtered data. For most Access users, VBA filtering is flexible and easy to use.

Using VBA to filter data helps you Build Search Form features that respond to user input and make searching fast and easy. You can expand your form with more search fields or link subforms for advanced filtering.

Multi-Field and Advanced Search

Add More Search Fields

You can make your search form more powerful by adding extra search fields. This lets you filter records using more than one piece of information. For example, you might want to search by both name and date. Allen Browne’s sample search database shows how to do this. You can use form controls, like text boxes or combo boxes, for each field you want to search. Then, you reference these controls in your query or VBA code. This method gives you dynamic and flexible search options.

Here are some ways to add multiple search fields:

1. Use the LIKE operator in your queries for each field. For example, set criteria like Field1 Like "*" & [Search1] & "*" and Field2 Like "*" & [Search2] & "*".

2. Combine several fields into one column and search that combined string.

3. Create a special search table that holds values from different fields. Update this table when records change.

4. Design your queries to handle empty search boxes. If a box is empty, the query returns all records for that field.

Tip: When you Build Search Form features with many fields, use clear labels and group related fields together. This keeps your form easy to use.

Use Queries for Multiple Tables

Sometimes, you need to search across more than one table. Access lets you do this by creating relationships between tables. You can join tables in the Relationships window by dragging a field from one table to a matching field in another. Once you set up these links, you can include several tables in your query. This helps you pull related data together, such as showing customer names from one table and their orders from another.

If you see too many repeated records, try using Group By in your query. This groups similar rows and removes duplicates. You can also change the join type in Query Design view. For example, you can show all records from your main table, even if there are no matches in the related table. This makes sure you do not miss any important data.

Search-As-You-Type

You can make your search form feel modern by adding search-as-you-type. This feature filters results as soon as you start typing. To do this, add a search text box and a clear button to your form. You can use a special function that updates the list or form as you type. The function changes the SQL query using the LIKE operator. It runs when you press a key or change the text in the box. The form then shows only the records that match what you typed.

Note: Search-as-you-type gives instant feedback. Users see results right away, which makes searching faster and easier.

By using these advanced techniques, you can Build Search Form features that handle many fields, work across tables, and provide instant results.

Clear and Reset Options

When you use a search form in Access, you often need a way to clear all fields and start fresh. Adding clear and reset options helps users avoid mistakes and makes your form easier to use.

Add Clear Button

You can add a clear button to your form to let users erase all search criteria with one click. This button is helpful when you want to remove filters or start a new search. Follow these steps to add a clear button:

1. Open your form in Design View.

2. Set the form’s Data Entry property to Yes. This setting makes the form show only new records.

3. Add a command button from the Controls group.

4. Open the property sheet for the button. Go to the Event tab.

5. In the On Click event, click the build button (three dots) and choose Code Builder.

6. Enter the following code inside the event procedure:

DoCmd.GoToRecord , , acNewRec

This code moves the form to a new blank record. It clears all bound controls and prepares the form for new input. If your form uses filters, you can also add these lines to remove them:

Me.Filter = ""
Me.FilterOn = False

Tip: If you want to clear only certain fields, set each field to an empty string or Null. For example, use Me.txtName = "" to clear a text box.

Reset Form Code

Sometimes, you want to reset every control on your form, not just the search fields. You can create a public VBA function that loops through all controls and resets them based on their type. This method works well for unbound forms, where controls do not link directly to data.

Here is a sample code block to reset all fields:

Dim ctl As Control
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            ctl.Value = Null
        Case acCheckBox, acOptionGroup
            ctl.Value = False
    End Select
Next ctl

This code checks each control and sets its value to Null or False. It skips controls that cannot be cleared. You can add error handling to avoid problems with locked or non-editable controls.

Note: Using a reset function keeps your form clean and ready for new searches. It also prevents errors like "No Current Record" that can happen if you try to clear bound fields directly.

---

You learned how to create a search form in Access by adding controls, writing VBA, and setting up filters. When you use a search form, you enter specific or partial information, and Access quickly finds matching records. This method saves time and helps you focus on the data you need. Try adding more fields or using combo boxes to make your form even better. As you practice, you will find new ways to improve your database and make searching easier.

FAQ

How do you make a search form show only matching records?

You set up filters using VBA or queries. When you enter search terms and click the search button, Access hides records that do not match. You see only the results that fit your search.

Can you search by more than one field at a time?

Yes, you can add more search boxes or combo boxes for each field. You write code or use queries that check all the fields. This lets you find records that match several pieces of information.

What if you want to clear all search fields quickly?

Add a clear button to your form. When you click it, the button runs code that erases all search boxes and removes filters. You can start a new search right away.

Do you need to know VBA to build a search form?

You do not need to know VBA for basic search forms. Access lets you use queries and macros. If you want more control or advanced features, learning some VBA helps.

Can you use drop-down lists for search fields?

Yes, you can use combo boxes as drop-down lists. These help users pick from set choices. Combo boxes make searching faster and reduce typing mistakes.