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.

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]”:

Step 6

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

Step 7

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

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:

• search_project_ID is the name of the text box that we renamed in Step 6.
• Project_Metadata is the name of the Table that contains the data that we need to search.
• project_code is the column in our table that we need to search.

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.

Comments

6 responses to “Add a simple search box to a Microsoft Access form”

On 10 November 2021, Clint wrote:

Might be a stupid question, I am a beginner… but where are the results of the search to be displayed once I type in the search box and hit enter?

Reply

On 24 February 2023, Josette Jansen wrote:

My code does not run, driving me bonkers

Reply

On 30 November 2021, Steve Leibovic wrote:

Thank you for these instructions. However a couple of minor points would make it more clear:
1. In your code you reference search_project_ID as the name of the text box renamed in step 6. However step 6 renamed it as search_projectID (missing one underscore) in the graphic
2. I can not get your Select query to work; I think there is a wrong number of ” or ‘ (double or single quotation marks). Rather I got this to work: Query = “SELECT * FROM Employees WHERE (([Employees].[LName])=[Forms].[EmployeesNew].[Search_Lname])” where my Form is [EmployeesNew], and the unbound text box for search on EmployeesNew is [Search_Lname]. Perhaps I am missing something in your quotes but I could not get it to work as listed.
Thank you!

Reply

On 28 March 2022, Alan wrote:

Works great except what does one do if you want to search for any part of the field?

Reply

On 26 November 2022, Edward wrote:

User wildcards in your query string or better still replace the text box with a combobox with the query to populate its dropdown field with data from the relevant table …

Reply

On 13 March 2023, amjad wrote:

Me.RecordSource = Query dose not work with me , i maked all thing like you writed

Reply

Have Your Say

Δ