Using an Unbound Form to Obtain Query Parameters

In this post we are going to use an unbound form to obtain the parameters for a query.  It follows on from my last post on How to Create a Parameter Query, but this time we are going to create the form which collects the query criteria.  This is particularly useful when the query has multiple parameters, and we want make the process of running our query more user friendly.

Create an Unbound Form

This object of this exercise is to query a simple customer table, returning a particular customer record in response to entering the customers name in an unbound form. Lets begin by creating the form which prompts the user to enter a firstname and lastname in two separate text boxes.  There is also going to be a Control Button which, when clicked, runs the query that references the two text boxes on our form.

Figure 1: The form to collect our Query Parameters.

1. Click the FORM DESIGN icon.  This is located in the FORMS group on the CREATE tab of the Access Ribbon.
2. Add the first text box to the form by clicking the TEXTBOX icon from the CONTROLS group and clicking on the desired position on the design grid.  
3. Select the textbox by clicking it and then click the PROPERTIES icon to bring up the PROPERTIES sheet.  Select the OTHER tab from the sheet.
4. We are going to refer to this text box as txtFirstName.  To do this we need to type FIRSTNAME into the NAME property of the PROPERTIES SHEET.
5. Next add a second text box to the form.  
6. Select the 2nd Textbox and enter txtSurname as the NAME property on the PROPERTIES SHEET.
7. Now we need to select the whole form by clicking the small square at the top left hand corner of the design grid. We are going to change some of the form properties to make the form look like a Dialogue box.
   
8. Select the FORMAT tab of the PROPERTIES SHEET.
9. Change the RECORD SELECTORS property to NO.
10. Change the NAVIGATION BUTTONS property to NO.
11. Change the BORDER STYLE property to DIALOG.
12. Select the OTHER tab of the PROPERTIES SHEET.
13. Change the POP UP property to YES.
14. Finish by saving your form as frmEnterParameter.

Before we add the command button, we will first create the Query to be run, and the table that it is based upon.

Create Parameter Query

This follows on from my last blog post on How to Create a Parameter Query .  Before you start creating the query you will first need to set up the table which is going to be queried.  This is a simple customer table with an ID, FirstName, and Surname fields. Add around ten random names that we can use as test data later on. Once you have done this you can create the actual parameter query following the procedure below.

1. Click the QUERY DESIGN icon to create a new query.
2. Select the customer table you just created from the SHOW TABLE dialogue box.
3. Select the fields to be used in the query.  The quickest way of doing this is to double click each field name  from the table box located above the design grid.  The fields to be used are  ID, FirstName, and Surname.
4. We now need to enter the query criteria.  To do this we are going to reference the parameter text boxes on the form we created earlier.  On the Query Design Grid, enter the following syntax in the criteria row of the FirstName and Surname columns respectively.

[forms]![frmEnterParameter]![txtFirstName]

[forms]![frmEnterParameter]![txtSurname]


The parameters entered by the user at runtime will then be used by Access as the Query Criteria.

This is how the grid should look when you have finished.

Figure 2: The Query Design.

You can now save the query as qryNameSearch.

Create Command Button

Now that we have created our query, we can go back to the form we created earlier and add a command button which runs the query when clicked.   To do this we are going to use the COMMAND BUTTON WIZARD.  Here is the procedure.

1. Open the form we created earlier in DESIGN VIEW.
2. Ensure the USE CONTROL WIZARDS icon is selected.  It is located in the CONTROLS group of the DESIGN ribbon, and should be highlighted in orange.
3. Click the BUTTON CONTROL icon (from the CONTROLS group), and position it on the design grid.  When you click on the desired position, the COMMAND BUTTON WIZARD begins.
   

   

   Figure 2: The Command Button Wizard.

4. Click MISCELLANEOUS for the category, and RUN QUERY for the Action. Then click NEXT.
5. Highlight the name of the Query we want to run.  The one we created earlier was called qryNameSearch.  Click NEXT.
6. Choose whether you would like text or a picture to be displayed on the command button.  Click NEXT.
7. Enter a meaningful name for the Command Button.  Lets call ours ctlRunQuery.

We are now in a position to try out our form and query.  Open the form in FORM VIEW and enter a customer name in the two text boxes.  You will need to use a name that you entered as test data when you set up the table.  Then click the Query Command Button.  The query should then run returning the customer record you just selected.