Access 2016: Create a Query with User Input
You can create a query that relies on user input. The value that the user provides determines the result set.
In Access, queries can accept parameters that can be used to determine the results of the query. When the query is run, the user is first prompted to enter a parameter (or multiple parameters). Once the parameter has been supplied, the query can return the results, based on the parameter provided.
For example, we previously created a query that returns all albums from Iron Maiden. This query could be modified so that it returns all albums from a given artist. The artist can be specified by the user.
Access makes this extremely easy to do. In fact, all you need to do is replace "Iron Maiden" with some text enclosed in square brackets.
So let's take a copy of the above query, and modify it so that the user gets prompted to enter an artist name when running the query.
Make a Copy of the Existing Query
Our new query is going to be almost the same as the previous one. So we can save some time and start with a copy of the existing one.
-
Copy the Existing Query
In the left navigation pane, right-click on the Iron Maiden Albums query and select Copy from the contextual menu.
-
Paste the Query
Now right-click again inside the navigation pane and select Paste.
-
Name the new Query
Name the query at the prompt and click OK.
-
Open the Query in Design View
In the left navigation pane, right-click the query and select Design View.
-
The Query Design
Replace Iron Maiden with [Which Artist?].
The square brackets will cause a parameter dialog box to open when the user runs the query. The text inside the square brackets will be displayed to the user, and an input field will be displayed for their input.
-
Run the Query
Now run the query (either by clicking Run or View in the Ribbon, or clicking the Datasheet View icon in the bottom right corner of the screen).
A prompt will display, asking for an artist. Enter an artist.
-
The Query Results
And if the artist you provide has albums in the database, those albums will be displayed.
Modify the Query
Now we can modify the copied query to prompt the user for a parameter.