Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

Add a simple search box to a Microsoft Access form

A step-by-step guide to adding a simple search box to a Microsoft Access form.

I have recently been developing a database in Microsoft Access; a really powerful tool for searching and querying data.

I created a Form (called Project_Metadata which is based on a Table of the same name, but needed to add a search box so that I could identify specific projects in my database by their project code. The project codes are contained in a column within the Project_Metadata Table called project_code.

Here’s how I created the search box:

Step 1

Open the Form that you want to add the search box to and select “Design View” from the menu via the “Design” ribbon.

Step 2

Insert a text box into the header section of the form by dragging it from the “Controls” pane.

Screen capture of the Controls ribbon in Microsoft Access
Click on the “text box” button to insert a text box into the header section of the Microsoft Access form.

Step 3

Rename the text box caption to “Search Project ID” (or other meaningful text).

Step 4

Right-click on the text box and select “Properties” to reveal the “Property Sheet”.

Step 5

Go to After Update and select “[Event Procedure]”:

Within the Property Sheet for the search box, click on “After Update” and select [Event Procedure].

Step 6

Go to the “Other” tab in the “Property Sheet” and rename the text box to something meaningful (eg “search_project_ID”).

Change the name of the search box to something meaningful. This name will be referenced in later Visual Basic code.

Step 7

Go back to the “Event” tab and click on ... beside After Update. This will bring up a Visual Basic window in Access.

Click on the “…” symbol beside “After Update” in the Property Sheet to access the Visual Basic code editor.

Step 8

Replace all of the text with the following:

Option Compare Database

Private Sub Search_Exp_AfterUpdate()

If search_project_ID <> "" Then
    query = "SELECT * FROM Project_Metadata WHERE project_code='" & search_project_ID & "'" & ""
    Me.RecordSource = query
    Me.Refresh
Else
    Me.RecordSource = "SELECT * FROM Project_Metadata"
    Me.Refresh
End If

End Sub

You will need to customise the above code for your own database. In the example above:

The Me commands stand for the Form that is currently in focus and whose code is now running, which is why it is used here.

Step 9

Check that your search box works in your Form. You should now be able to enter a term and return all records that contain the string that you enter.

An example of part of a header section in a Microsoft Access form, showing the search box that was created (alongside other buttons).
   

Comments

No comments have yet been submitted. Be the first!

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.