Access Cookbook - Ken Getz [41]
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
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
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