Microsoft Access 2010 Bible

Chapter 25: Advanced Data Access with VBA

893

will actually run. The content of controls like combo boxes or list boxes can be dynamic and con-

tain actual values from the underlying tables. This means that the criteria controls might contain

only the names of customers who’ve placed orders, or product categories actually in the database

at the moment.

Figure 25.10 shows frmFilterProducts in Design view. cboCategory is filled with the data

from qryCategories, which sorts the records in tblCategories in alphabetical order.

FIGURE 25.10

Creating a dialog box for selecting records

cboCategory’s DefaultValue property is set to Cars because this is the most commonly

used criteria for the Products form. In this case, LimitToList is set to No because I want to force

users to select only from the categories actually in tblCategories.

Figure 25.11 shows qryProductsFormReference. This query selects all fields in tblProd-

ucts based on the category retrieved from cboCategory on frmFilterProducts. Notice the

criteria expression in the Category column:

= [Forms]![frmFilterProducts]![cboCategory]

One very nice new feature in Access 2010 is that, as you type the form reference into the criteria

box, IntelliSense helps you choose the control on the form to use (you can see this in effect in

Figure 25.11). Unlike previous versions of Access, in which you had to know the exact name of

the form and its controls, Access 2010 helps you out when composing this expression.

As the query runs, it automatically retrieves the criteria value from cboCategory. The combo

box returns Cars, unless the user has choosen a different category.