How to Query in Form View in Access

Queries help find records within a large database, based on specific criteria.

Microsoft Access is a database program with the power to sort and display records through queries. These queries can be cumbersome and difficult to manage. Creating a form that acts as the front end for a query helps users to easily search records in a simplified form. The process of creating these queries only takes a little while but requires some background knowledge on Microsoft Access. These steps may also vary somewhat between the different versions of Access.

Instructions

1. Open your Access database. You can do this by either double-clicking the database file or opening Microsoft Access and selecting "File" and then "Open" from the menu.

2. Create a new form selecting "Forms" on the left-hand side of the database window and double-clicking "Create new form in design view." Place text boxes within it. Each text box will represent a search option for your data. Be sure to remember what you put in the "Name" field of each textbox's properties window, as these will be important in creating the query

3. Add a button to the form labeled "Search." In the "OnClick" properties of the button, set it to open a new macro. Remember the name you gave to this macro.

4. Create a new macro named exactly as you defined in the button in Step 3 (e.g.: if you told the button to run the macro "NewMacro," then make a macro called "NewMacro"). You create a macro by selecting "Macro" on the left-hand side of the database window and then clicking the "New" button at the of the same window. Set the action to "OpenQuery" with the pull-down menu and give a name for the query you wish to run under the "QueryName" field. Remember the query name for the next step.

5. Create a new query based on the table of data you wish to search by selecting "Query" and either clicking the "New" button above or double-clicking "New Query in Design View." For each form you want to search, set the "criteria" to equal the test box input from the form you created in Step 2. The text query should look like (minus quotes) "Form![Form name]![Textbox Name]" where "Form name" is the name you gave to your form, and "Textbox Name" is the name of the textbox(es) defined in Step 2. Do this for each area of your database you wish to query using your form.

6. Test the new form by double-clicking on it in your database. When you enter something in the text box and press "Search," it should return a datasheet view of all those records that match the criteria entered in the form.

Tips & Warnings

• Note that leaving a field blank in the form tells the query to find all records. This behavior can be changed.

• This is an inclusive search. This means that if you have terms entered in multiple textboxes, the query will return all records that match any of those terms, not just those records that match all of the textboxes. This can be changed via advanced settings in the query.

• You can make query results look more professional by outputting them to a report format.

References

• Microsoft: How to Use the Query by Form in Microsoft Access