Microsoft Access 2010 Bible

Chapter 25: Advanced Data Access with VBA .............................................................................

883

The combo box contains two columns selected by the query shown in Figure 25.3. The first col-

umn, LongDescription, joins ModelYear and Description from tblProducts. The sec-

ond column is the ProductID field in tblProducts. The ProductID column serves as the

bound column for the combo box and is the value returned by the combo box when a row is

selected in the combo box. The second column’s width is 0, which hides the column when the

combo box list is pulled down.

Note
In the code examples that follow, you’ll see references to cboQuickSearch.Value. Keep in mind that the
value of the combo box is the Product ID of the item selected in the combo box.

FIGURE 25.3

The query behind the Row Source property of cboQuickSearch

This combo box is used for several of the examples in this chapter. Next you see how to find

records in a variety of ways using the combo box and the code behind it.

Using the FindRecord method

Let’s take a look how the quick search combo box on frmProductsExample1 works. Selecting

a product category from cboQuickSearch fires the AfterUpdate event. Code in the

AfterUpdate event procedure performs the search on the form, and the form instantly displays

the selected record.

The FindRecord method locates a record in the form’s bound recordset. This is equivalent to

using the binoculars in the Access ribbon to find a record in a datasheet.

When performing a search on a datasheet, you begin by clicking on the column you want to

search, perhaps LastName. Next, you click on the binoculars in the ribbon to open the Find and

Replace dialog box, and enter the name you want to find in the recordset. Access knows to use the

LastName field because that’s the column you selected in the datasheet. When you enter Smith as