In the previous tutorial, we explored how to create simple parameter queries in Microsoft Access which prompt the user for criteria when run. These are real bread-and-butter queries you'll find yourself using over and over. The only drawback with such queries is that you have no control over the look of the prompt dialog boxes displayed and, with complex queries, you may end up presenting your users with prompt after prompt after prompt.
Your database application will be much more enjoyable to use if you eliminate this dialog box proliferation and spruce up the dialogs. To do so, you'll need to create a custom dialog box which consolidates all the prompts.
That query was designed to elicit the following information:
Show me countries that joined the UN between date x AND date y AND which have a population over n.
If we create such a query without using a custom dialog box, we'll have to badger our users with three consecutive dialogs, the first asking Joined the UN after what date?, the second asking And joined before which date?, and the final prompting And has a population over? With our custom dialog, we can consolidate these questions and also gain control over the appearance of the dialog box itself.
We'll build the form first, then recreate a modified version of the query which takes advantage of the custom dialog box.
I'll describe the process using Access 2000. Access 97's interface is a little different, but you should be able to adapt these instructions with very few changes.
- Open the Countries database and, in the main database window, click the Forms button, then double-click Create Form In Design View. You'll see a blank form titled Form1: Form.
- If it's not already displayed, display the Toolbox by clicking the Toolbox icon (a crossed hammer and spanner) on the toolbar. Make sure the Control Wizards are enabled (the Control Wizards icon at the top of the Toolbox should be depressed). Drag the toolbox to the side so it's easily accessible but doesn't obscure the form window. Take some time to run your cursor over each of the Toolbox icons so you can see what it contains.
- We want to add a data entry box for each of our three prompts. So click the Text Box tool in the Toolbox, then click-and-drag on the form to create a rectangular text box (position it a little down from the top and about two-thirds of the way from the left edge). You'll see that a label is created to the left of your form. Click the label to select it and resize it by dragging the resizing handle at its left edge towards the left of the form. Click within the label, delete the existing text and type Show countries which joined the UN after:, then click the form outside the label. You'll see that the Text Box contains the word Unbound. This indicates that the text box is not connected (bound) to data in a field, as is the case with our data entry forms. In fact, the whole form is called an unbound form because it is not based on any existing data or data structure.
- Repeat step 3 for the remaining two prompts, lining them up one beneath the other. Place the text and joined before: in the second prompt and and whose population exceeds: in the third prompt.
- Each text box needs an easy-to-remember name so we can refer to it easily in our query, so right-click the first text box and choose Properties from the pop-up menu. This displays the Properties box, which lets you adjust every facet of your text box. Position the Properties box to the side, so you can see it and the Toolbox while also viewing your form.
- In the Properties box, click the All tab and change the value of the Name property to FirstJoined.
- Now do the same for the other two text boxes. Click the second text box (the Properties box will now reflect the settings for that box) and change the Name property to LastJoined. Click the third box and change its Name property to Population.
To create a query that grabs its parameters from our custom form:
- In the main database window, click the Queries button and double-click Create Query In Design View.
- Select the Countries table and click Add to add it to the query, then close the Show Tables dialog.
- Drag the fields Name and JoinedUN onto the query grid, then drag the Population field onto the grid to the right of the JoinedUN column.
- In the Criteria cell of the JoinedUN column, type:
Between [Forms]![Criteria]![FirstJoined] AND [Forms]![Criteria]![LastJoined]
Eek! I know this looks scary, but that [Forms]![Criteria]![FirstJoined] stuff simply tells Access to go check its collection of forms, locate one called Criteria, and then find the value entered into the FirstJoined text box on that form. You can think of it as the equivalent of telling Access the address where it can find the data, with each part of the address enclosed in square brackets and separated by an exclamation mark. Note that instead of using greater than (>) and less than (<) symbols, we've used the shorthand BETWEEN…AND operator, which you can use when searching for dates.
- You should be able to guess what's coming next. In the Criteria cell of the Population column, type:
- Right-click in the grey area above the query grid and choose Parameters from the pop-up menu.
- In the first Parameter cell, type:
then tab over to the Data Type cell and select Date/Time from the drop-down list.
- In the second Parameter cell type:
and select Date/Time data type.
- In the third Parameter cell type:
and select Long Integer in the data type list.
- Click OK and close the query, saving it with the name UNPopulation.
- Open the Criteria form in Design view.
- Click the Command Button tool in the toolbox then draw a rectangle about three centimetres wide and one centimetre high at the bottom of your form. The Command Button Wizard will be displayed. (If it isn't, it means you haven't enabled Control Wizards in the Toolbox. Select the button you just created and press the Delete key to erase it, then enable the Control Wizards and try again.)
- In the Categories list select Miscellaneous and in the Actions list select Run Query, then click Next.
- Select the UNPopulation query from the list and click Next.
- Select the Text option, type Display Matches and click Next.
- Name the button RunQuery and click Finish.
- Save and close the form.
- We created a form containing a text box for each parameter in our query.
- We gave each text box a recognisable name.
- We created a query containing criteria which derive their parameters from the values typed into the text boxes on our custom form.
- We assigned each parameter a data type to ensure Access knew how to deal with it.
- We added to our form a command button which runs the query.
- Click Forms in the database window and double-click the Criteria form. (If the Properties box opens when you open the form, click its Close button to close it.)
- Type the following values in the three boxes on the form:
and click the Display Matches button. (Note, if you use a different date format on your PC, such as MM/DD/YY, adjust the second value accordingly.)