Find a Record using a Combo Box

For example, the Students Form shown in Figure 10 below, shows the first of 197 students contained in the students table. Suppose you need to update the record for David Kennedy. How would you locate his record? In this article we’ll review three methods available to find a record from within an Access form. You can follow along with this article by downloading the sample database. download sample data

Figure 10 The Students Form

The first method is to use the navigation buttons at the bottom of the form. You could click the next record button to advance to the next record, then the next, until you arrived at David Kennedy’s record. Of course, this is an inefficient way to find a record.

The second method is to use the Find and Replace dialog box. To initiate the Find and Replace dialog box, first click in the field you want to search through, then click Edit – Find using the menus at the top of the Access window. Type a phrase or just part of a phrase that is contained in the field. In our example, click on LastName; then click Edit-Find to launch Find and Replace; now type Kennedy and press Enter to have the Kennedy record automatically displayed. If there are multiple Kennedy records, click Find Next and Access will advance through the recordset until it finds all the matches. Our example is shown in Figures 11, 12, and 13 below.

Figure 11 Set the focus on LastName

Figure 12 Using Find and Replace

Figure 13 Locate the David Kennedy record

The third method to find a record while using a form requires you to add a Combo Box to the form. This will require learning a new skill, but it will be a valuable tool for all of your Access forms in the future. To add a combo box, follow these steps:

1. Open the Students Form in design view.

2. Select all the fields on the form by clicking the first one, holding the shift, clicking the next, until all have been selected. Then move all the fields down slightly (approximately .25") to make room for the combo box.

3. Locate the Control Wizards icon on the Toolbox and click it to turn it 'on’ if necessary as shown in Figure 14. This icon will allow the Combo Box control wizard to launch when selected. When the button is in the off position, the Combo Box wizard will not launch.

   FIGURE 14 The Toolbox

   

4. Click the Combo Box icon and then click at the top of the detail section on the Students form just above the StudentID field as shown in Figure 15.

   FIGURE 15 Select Combo Box then click in the Detail Section

   

5. Choose the 'Find a record’ option when the 'Combo Box Wizard’ asks, 'How do you want your combo box to get its values?’ This step is shown in Figure 16. Click Next.

   Figure 16 The Combo Box Wizard (step 1)

   

6. Add the StudentID, LastName, and FirstName fields at the next prompt. See Figure 17. These are the fields that the user will see when the Combo Box is used. Click Next.

   Figure 17 The Combo Box Wizard (step 2)

   

7. The next prompt allows for the width of the columns to be modified. Notice in Figure 18, which is step 3 of the Wizard, you only see the LastName and FirstName columns. The first column, StudentID, is hidden because you don’t want to show this field to the user. It is more likely the users will know the LastName of the student they’re trying to find, not the StudentID. Stretch the column widths of the LastName and FirstName columns as needed. Click Next.

   Figure 18 The Combo Box Wizard (step 3)

   

8. Type "Find by Last Name" for the label at the next prompt. See Figure 19. Click Finish.

   Figure 19 The Combo Box Wizard (step 4)

   

9. Click the View icon and select Form View to view the Students form and see the results. Figure 20 shows the finished Combo Box. To test the combo box, put the cursor in the new combo box field, type 'Kennedy’ and press 'Enter.’ The David Kennedy record is automatically displayed as shown.

   Figure 20 The finished Combo Box

   

Conclusion

The three methods to find a record will each work. However, most users prefer the combo box method. Add a combo box to one of your forms and test the results for yourself.