Hack 79. Help Users Drill Down to a Record

Facilitate browsing through a lengthy customer database by first grouping customers.

Sometimes users need to spend a lot of time finding a specific customer from a long list of customer records. For example, loading customer names into a combo box or a listbox requires your users to do a lot of scrolling.

Here's a technique that removes some of this drudgery. The first step is to provide a way to list smaller groups of customers. Figure 8-10 shows a form in which four buttons (on the left, inside the Browse Customers frame) lead to customers whose last names start with AF, GL, MR, and SZ.

Figure 8-10. Browsing customers within alphabetic groupings

Segregating the customers into four groups is subjective. You can apply this technique to even single letters; in other words, you can have 26 buttons. The more customers there are, the smaller the groupings should be. Keep in mind that the point is to present smaller lists of customers for users to browse through. If you have thousands of customers, a grouping such as AF still results in a rather lengthy list.

Clicking any of the four buttons opens a form that is filtered to customers whose last name falls into the indicated grouping. For example, the code in the Click event for the AF button looks like this:

 Private Sub cmdAF_Click( )
   DoCmd.OpenForm "frmBrowseCustomers", acNormal, , _
   "CustomerLastName Like'A*' or CustomerLastName Like 'B*' or " & _
   "CustomerLastName Like'C*' or CustomerLastName Like 'D*' or " & _
   "CustomerLastName Like'E*' or CustomerLastName Like 'F*'"
 End Sub

The OpenForm method opens the frmBrowseCustomers form filtered to the appropriate customers. Figure 8-11 shows the form opened with the AF customers.

Figure 8-11. A filtered set of customers on a form

The form in Figure 8-11 really serves just as a browse feature. Users still don't see the full customer record. To see the full record, the user must double-click any record shown on the browse form, which opens the full customer record. The form that displays customer details is filtered to the particular record selected in the form shown in Figure 8-11.

To make this happen, the key is necessary. The key, an AutoNumber type named CustomerID in this example, is on the browse form, but it is hidden. Figure 8-12 shows the design of the browse form. In Design mode, you can see the key field.

All the fields in the browse form have code in their double-click events. The code calls a subroutine named open_customer, which opens the customer detail form. This time, the OpenForm method uses the key to open the detail form. Figure 8-13 shows the code behind the browse form.

Double-clicking a record in the browse form pulls up the customer's details. Figure 8-14 shows the customer detail form.

To summarize, when working with a large number of customers (or with other types of data, for that matter), users can get to the desired detail records pretty quickly by grouping the records into smaller browseable sets.Then, from the interim browse form, they can view the full details. Using this technique makes it easy to get to a detail record with just a few clicks, without having to scroll through a long list.