|[ Team LiB ]|
Recipe 2.4 Use an Option Group to Collect and Display Textual Information
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.
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.
To create a minimal sample form that works with the same data, follow these steps:
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.
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.
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:
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.
|[ Team LiB ]|