Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. 
   In 02-04.MDB, create a new form. Choose
   tblShipments for the form''s RecordSource property.

   
   

2. 
   Create controls on your new form, as shown in Table 2-2. Make sure that you''ve
   created the option group before you attempt to place any option
   buttons inside it. The option group should turn dark when you attempt
   to place an option button in it.

   
   

3. 
   Create the following event procedure in
   the form''s OnCurrent event:

   
   

   Private Sub Form_Current( )
   Me.grpCode = Switch( _
   Me.txtShipper = "UPS", 1, _
   Me.txtShipper = "Fed Ex", 2, _
   Me.txtShipper = "US Mail", 3, _
   Me.txtShipper = "Airborne", 4)    
   End Sub

4. 
   Create the following procedure in the
   option group''s AfterUpdate event:

   
   

   Private Sub grpCode_AfterUpdate( )
   Me.txtShipper = Choose( _
   Me.grpCode, "UPS", "Fed Ex", "US Mail", "Airborne")
   End Sub

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.