Recipe 2.4 Use an Option Group to Collect and Display Textual Information

2.4.1 Problem

Option groups are great for collecting and displaying numeric values, but sometimes you need to use an option group bound to a column of values that isn't numeric. For instance, in each row you have a field that contains just one of four different alphabetic codes. You want some way to let the user choose from those four codes on a form.

2.4.2 Solution

When you want a control on a form bound to a column in a table that contains a few alphabetic items, you usually can use a list or combo box to display and collect the information. Sometimes, though, you want to be able to use an option group, where you can have option buttons or even toggle buttons containing pictures. But option groups, as Access implements them, can be bound only to numeric columns.

The solution is to use an unbound option group. Rather than moving the data directly from the form to the underlying data, you'll make a pit stop along the way.

Open and run frmOptionExample in 02-04.MDB. This form, shown in Figure 2-7, pulls in two columns from the underlying table, tblShipments. Each row contains a Contents field and a Shipper field. The Shipper field can be just one of four values: UPS, Fed Ex, US Mail, or Airborne. The form displays the Contents field in a text box and the Shipper field in an option group. It also shows another text-box control: the pit stop mentioned earlier. This (normally hidden) text box is the bound control, not the option group.

Figure 2-7. Example form using an option group to store character data

To create a minimal sample form that works with the same data, follow these steps:

Table 2-2. Control properties for the new sample form

Control type	Property	Value
Option group	Name	grpCode
Option button (UPS)	Name	optUPS
	OptionValue	1
Option button (Fed Ex)	Name	optFedEx
	OptionValue	2
Option button (US Mail)	Name	optUSMail
	OptionValue	3
Option button (Airborne)	Name	optAirborne
	OptionValue	4
Text box	Name	txtShipper
	ControlSource	Shipper

2.4.3 Discussion

Using just two simple event procedures, you've managed to make the sample form store the data as required. The example works because of two distinct events and two distinct VBA functions that you call from those events.

The form's Current event occurs every time you move from one row to another in the underlying data. In this case, you'll need to convert the data from its raw form (as the shipper's code text strings) into a format that the option group on the form can display for each row as you move to that row.

The option group's AfterUpdate event occurs whenever you change its value. For this control, choosing any of the option buttons within it will trigger the event. Use this event to place a new value into the text box on the form, which is directly bound to the correct column in the underlying data.

When you want to convert the raw data into an integer representation (so the option group can display the value), use the Switch function. Its syntax is:

returnValue = Switch(expr1, value1 [,expr2, value2][, expr3, value3]...)

Access will evaluate each of the expressions but will return the value corresponding to the first one that returns a True value. In this example, the Switch function assigns the value of this expression:

Switch([txtShipper] = "UPS", 1, [txtShipper] = "Fed Ex", 2, _
 [txtShipper] = "US Mail", 3, [txtShipper] = "Airborne", 4, Null, Null)

to the option group. If the value in [txtShipper] is "UPS," the option group gets the value 1. If [txtShipper] is "Fed Ex," the option group is 2, and so on. The final pair (the two Null values) ensures that if the value of [txtShipper] is Null, the option group will be Null too. Access calls this function from the form's Current event, so that every time you move from row to row, Access assigns the appropriate value to the option group based on what it finds in the bound text box.

To convert a choice made in the option group into its appropriate text value to be stored in the table, use the Choose function. Its syntax is:

returnValue = Choose(index, value1 [, value2][, value3]...)

Based on the value in index, the function will return the matching value from its list of values. In our example, the code assigns the value of this expression:

Choose([grpCode], "UPS", "Fed Ex", "US Mail", "Airborne")

to the bound text box once you've made a selection in the option group. If you choose item 1 from the option group, it'll assign "UPS" to the text box. If you choose option 2, it'll assign "Fed Ex," and so on.

You can use the two events (After Update and Current) and the two functions described here to handle your conversions from integers (option group values) to text (as stored in the table), but you should be aware of a few limitations that apply to the Switch and Choose functions:

• Both functions support only a limited number of options. Switch can support up to seven pairs of expressions/values. Choose can support up to 13 expressions. If you need more than that, you'll need to convert your event handlers to VBA. Of course, you should avoid putting more than seven items in an option group anyway.

• Both functions evaluate all of the expressions they contain before they return a value. This can lead to serious errors unless you plan ahead. The following expression details the worst possible case:

  returnVal = Choose(index, MsgBox("Item1"), MsgBox("Item2"), MsgBox("Item3"), _
   MsgBox("Item4"), MsgBox("Item5"), MsgBox("Item6"), MsgBox("Item7"), _
   MsgBox("Item8"), MsgBox("Item9"), MsgBox("Item10"), MsgBox("Item11"), _
   MsgBox("Item12"), MsgBox("Item13"))

  You might assume that this expression would display the message box corresponding only to the value of index, but in fact it will always display 13 message boxes, no matter what the value of index is. Because Switch and Choose both evaluate all of their internal expressions before they return a value, they both execute any and all functions that exist as parameters. This can lead to unexpected results as Access runs each and every function used as a parameter to Switch or Choose.

  In most cases, you'd be better off using a list or combo box with a separate lookup table, allowing your users to choose from a fixed list. If you have a small number of fixed values and you need to store those values in your table (as opposed to an index value from a small lookup table), the technique presented here should work fine.

To use the techniques outlined here in your own applications, you'll need to modify the screen display and the code. Once you've done that, you should be able to use an option group to gather text information.