MS Access: Adding Parameter Queries to a Form

The easiest way to add a query to a form is to:

• Design the query that restricts the type and category information you want the form to display. Test the query and save it. The query is now in your database object inventory.
• Use an existing form or design a new form that will display the fields and perform the calculations to maintain your database. Then do the following:
  • Open the property sheet in the Tools area of the Design tab.
    
  • On the Data tab of the Property sheet, click on the Record Source down arrow and select the parameter query name and save the form.

Let’s use as an example a large customer and sales database with four distinct sales areas (north, south, east, west). The input form to record and update this database could be based on no query, a select query, or a parameter query.

A Form Based on no Query:

As a database grows in number of records, pretty soon accessing and updating records already entered becomes cumbersome. Each time the database form is opened, the first of all the forms in the database is displayed. To correct or update a previously entered form, it has to be located among the many others. A search based on some field criteria must then be done, adding an extra step. It is time to add a query to your form.

A Form Based on a Select Query:

To alleviate the aforementioned situation, we could add a select query to our form and save that form under a different name. For example, using our customer sales database, we could designate one form for north, one for south, etc. We could even change the appearance of the form so that each sales area input could be restricted to certain employees who maintain a networked database.

The select query could have one or more criteria, including a date range, that will display a narrow category of records. The advantage of a select query is that it works in the background and essentially restricts access to only those records that meet the multiple criteria of the select query.

A Form Based on a Parameter Query:

A parameter query is far more flexible than the select query. Attach a parameter query to a form and a dialog box will appear. The dialog box needs user input and the query will retrieve those records that meet the criteria. Returning to our sales database example, we could design a parameter query that asks the user to input the sales area into the dialog box. This one parameter query takes the place of four select queries.

Other Advantages of Attaching a Parameter Query to a Form

As our business year progresses, our select queries must be updated to display our most current forms. Parameter queries remain robust and intuitive, whereas select queries tend to become obsolete and forgotten. Parameter queries keep our database user friendly, especially when more than one person maintains the database.

(See MS Database Queries: The Economy of Parameter Queries.)

MS Office Online: Make a query ask for input

Customizing Access Parameter Queries