Microsoft Access 2010 Bible

Part IV: Professional Database Development

894

FIGURE 25.11

Creating a query that references a form control

In normal operation, the user selects a product category from frmFilterProducts and clicks

OK. Code behind the button opens frmProductsExample4, which is bound to qry

Products_FormParameter. The criteria for the Category field in qryProducts_

FormParameter looks up the selected value in cboCategory on frmFilterProducts, and

magically frmProductsExample4 opens with just the selected product category loaded.

The only issue facing developers working with tightly integrated database objects like this (in this

case, frmFilterProducts, qryProducts_FormParameter, and frmProducts

Example4) is that it may not be obvious that the objects work together. Removing or modifying

any of these objects might break the workflow, or cause problems for the users.

You might choose to use a naming convention that implies the relationship between the two forms

and the query, such as giving each item the same name, but with different prefixes. Or, you could

use the custom groups in the Access Navigation Pane, and add the objects to a single group.

Very often things that are obvious to you — the original designer and developer — may not be as

clear to someone else, so it pays to take advantage of simple techniques that help document your

applications.

Linking the dialog box to another form

The frmFilterProducts dialog box (you saw this back in Figure 25.10) does more than just

create a value that can be referenced from a query. It also contains code to open frmProducts

Example4.

Figure 25.12 shows the cmdCancel_Click and cmdOK_Click event procedures behind the

Cancel and OK buttons found on frmFilterProducts.

The cmdOK_Click event procedure code opens frmProductsExample4, sets the focus on it, and

then re-queries the form to make sure that the latest selection is used on the form. The SetFocus

method is necessary to move focus to the form that is opened. The Requery method isn’t strictly

required, because a form automatically re-queries its record source the first time it’s opened.

However, if the form is already opened — for example, if you use the dialog box a second time to

search for another record — the Requery method ensures that the form displays fresh data.