Representing Sets of Values

A number of controls allow you to display sets of values on a form. The choice is largely determined by whether you need to capture a single value (albeit drawn from a range of values) or several.

Capturing a Single Value from a Set of Values

The acceptability of a given value is often determined by its existence in a list. For example, the CustomerNumber value in an Orders record might be valid if and only if the customer number exists in the Customers table. Given that you want to limit the user input to the range of acceptable values wherever feasible, you'll want to present users with the list of Customers and allow them to choose one.

The controls most often used in this situation are the combo box and the list box, shown in Figure 15-3. The two have a few differences in functionality, the most important being that the combo box allows users to enter values that are not already in the list. As discussed elsewhere, this functionality can sometimes be used to create records for a linked entity. Even if entering new records isn't appropriate for your specific environment, the ability to find a value by typing it directly is preferred by most touch typists, who tend to find reaching for a mouse, or even looking at the screen, an inconvenience. For this reason, I prefer combo boxes in most situations.

Figure 15-3. Visual Basic provides three styles of combo boxes.

Visual Basic allows combo boxes to be configured to display their lists all the time or only on demand. Access allows only drop-down combo boxes. (Additionally, list boxes in Visual Basic can display check boxes next to each value; Access doesn't support this functionality.)

The Windows Interface Guidelines for Software Design states that you should size simple combo boxes and list boxes to display between three and eight items. If you can't find the room on the form to do this comfortably, you should use a combo box with a drop-down list.

Even if you have the space to display the list permanently, you should use a drop-down combo box if the list is irrelevant once users make a choice. If you're providing a list of customers on an Orders form, for example, users don't care what other customers are known to the system once they've chosen the customer they want.

At times, it can be useful to allow users to see at a glance what values are possible, particularly if either the list itself or the value assigned to the record changes frequently. In a library system, for instance, in which titles are assigned to a subject category from a list that is frequently updated and refined, users might want to see the most current list to determine whether a new category better defines a given book.

An extremely useful capability in both Visual Basic and Access is being able to display a value other than the one being stored in the table. If you're using a counter or identity value as the primary key in the Customers table, for example, you'll need to store this value as the foreign key in the Orders table, but there's no reason to make users look at it. In the Orders form, you can easily display customer names (from the Customers table) for users to choose from instead.

In Visual Basic, you do this by specifying a different value for the DataField and ListField properties. In Access, you use multiple columns—one for the customer name and one for the customer number—bind the control to the Orders customer number column, and hide the customer number column by setting its width to zero.

The ability of Access combo boxes to display multiple columns is extremely useful, and it has always seemed a pity to me that it's not possible in Visual Basic. In showing a list of customers, for instance, it's a good idea to include some additional information, such as the city in which they live, to further identify the customer. In Visual Basic, you can do this only by using a calculated field that concatenates the values as the ListField property setting. Somehow this never seems to work quite as well as Access combo boxes, particularly if some values are empty.

As useful as list boxes and combo boxes are, they aren't practical if the list contains too many values. With more than a couple hundred items, you'll need to find some way of limiting the list. You could have users select a letter of the alphabet, a sales region, or the state of residence, perhaps, and then filter the list items based on this selection.

If the list is very short—no more than five or six items—and if its values are fixed, you might want to use an option group rather than a combo box or list box. You can implement option groups using either option buttons or toggle buttons, as shown in Figure 15-4. Option buttons are the more usual choice.

Figure 15-4. Option buttons or toggle buttons contained in a group box can display short fixed lists.

Although it's possible, at least in Visual Basic, to generate an option group at run time, it's not a good idea to do so. It's disconcerting to users for a form layout to change, as it will do if you're adding and deleting options on the fly. So unless the option list is permanent (or at least fixed until the next release), you're better off using a list box or combo box. The size of these controls stays the same when list items are added or removed, so it's not uncomfortable for users if the list items change.

Capturing a Set of Values

If you need to capture a set of values, you're working with the many side of a one-to-many relationship. As we saw in Chapter 14, the first decision you must make in these situations is whether you need to display and capture the records all at once or one at a time.

If you want to display and capture the many-side records one at a time in Access, you can use a subform in single-record view. Set the LinkChildFields and LinkMasterFields properties, and Access does the majority of the work for you. In Visual Basic, creating such a subform requires a little more work (OK, a lot more work), but in return you get slightly more control. Either way, this is a good technique if you have several field values to capture, and particularly if you want to use a variety of control types.

If you need to collect multiple values per record and want to display multiple records simultaneously, you might be able to use a datasheet in Access (as shown in Figure 14-2) or a Grid control or Microsoft Hierarchical FlexGrid control in Visual Basic. Access datasheets are quite powerful (sometimes too powerful), and they allow several control types in addition to text boxes. Visual Basic grid controls are, frankly, a bit painful to implement, and Hierarchical FlexGrids allow data only to be displayed, not edited.

Fortunately, both Access and Visual Basic provide alternatives for when you want finer control over the form display. The Access subform control supports the display of subforms in continuous form view so that multiple records can be visible simultaneously. Visual Basic 6 introduced the DataRepeater control, which provides essentially the same functionality, although it's implemented quite differently. Either control is a good choice if you need to display multiple records and also need to use a control type—such as an option group—that isn't supported by a datasheet or grid control. Both controls allow each record to occupy multiple lines.

Another control that can be useful for displaying multiple records in some circumstances is the tree view control. The tree view control is most often used to display hierarchical data in outline form, but it can also be used to display selected details for each record. The tree view is not an effective mechanism for editing this detail, but it can be very useful for negotiating records. Using a paradigm similar to Microsoft Windows Explorer, you can show details regarding the selected record in another section of the form.

Finally, in situations where users need to select a set of items from a list, a linked pair of list boxes can be a useful structure. This structure, shown in the Sample Fields and Fields In My New Table lists in Figure 15-5, is frequently used in wizards. (The screen shown is from the Access 2000 Table Wizard.) It's easy for users to understand list boxes linked in this way, but be warned, they're difficult to implement.

Figure 15-5. The Access 2000 Table Wizard uses linked list boxes to allow users to choose fields for the new table.

A pair of list boxes like this is convenient during initial data entry, but it's not an appropriate structure for subsequent editing and display, if for no other reason than that it takes up too much space. This is why the technique is most often used in wizards, where each bit of data entry can be handled on a separate screen. Once the initial data entry is complete, the user probably won't need to refer to the full lists of chosen and unchosen values, and you can more effectively use one of the controls listed previously or even a single multiselect list box.

However, because of the way selection works in multiselect list boxes, they can be dangerous. It's very easy for users to accidentally deselect all the currently selected items by single-clicking (rather than control-clicking) a new item. When the selections are bound to data, sorting out the record additions and deletions and reselections can be an absolute nightmare. A normal, single-select list box that only shows the items that have been selected is a better solution, perhaps in combination with a text box or combo box for adding items.