• Download the demo file
• Casebook Home

Making Better Option Groups

Option buttons are a good way to offer the user a restricted number of choices when entering data into a database field. But how just how to use them can be a bit confusing.

To Group or Not to Group?

The Access form design toolbox offers three kinds of control buttons: the Option Button (often called a Radio Button), the Check Box, and the Toggle Button. All types of control button can be used alone or as a group:

When used on their own (i.e. not as a member of an Option Group) the toggle button has a value of True or False (i.e. Yes or No). Used alone, control buttons are normally bound directly to a field and place their value into that field. It is usual, although not essential, to set a default value to true/false fields. If no default value is set, and the user makes no choice, a control button can have an initial value of Null.

The different states of the various types of control button are shown in the illustration below. When the user clicks the button its state changes. Option buttons and check boxes appear greyed out when they are null, but a toggle button appears no different.

Alternatively, you can use the control buttons as a group. An Option Group is a frame containing a collection of control buttons. It is customary to use Option Buttons in a option group but you can use check boxes or toggle buttons if you want. If you use the Option Group Wizard you are offered the choice of all three.

To build an option group you use the Option Group tool on the form design toolbox. If you want the (very helpful!) wizard to guide you through the process you should make sure that the Control Wizards button on the toolbox is selected.

If you prefer to work without the help of the wizard, you can use the option group tool to draw the frame, then add your control buttons, and set up everything manually.

Control buttons behave quite differently when they are grouped together inside a Frame. Instead of true or false, each button within the frame has its own numerical value. Only one button can be chosen at a time (hence "option"). When the user chooses a particular option that value is passed to the group. You can choose what that numerical value is (either when using the wizard or by setting the Option Value property of the button). If I were assigning values to a group of option buttons I would probably use 1, 2, 3... etc. unless I has a special reason to do otherwise.

An option group can only hold a numerical value (data type Long Integer). Suppose you had and option group with three buttons labelled "London", "Paris" and "New York" with option values of 1, 2 and 3 respectively. If the user chose the button labelled Paris the option group would have a value of 2.

Bound or Unbound?

If the option group is bound to a table field, its value gets placed directly into the field. When the user chooses Paris the number 2 is placed in the field. The City field of the table will contain a collection of numbers, not the names of cities. It's up to you to interpret these numbers when you view the table or build your queries and reports.

Alternatively, you can "translate" the numbers into your desired values before they are placed into the field. This means using a macro or VBA code and will probably involve using an unbound option group. Here's how I prefer to do it...

1. The first of all create an option group which is not bound to a field. If you build your option group with the wizard, Step 4 asks you what you want to do with the value of the selected option. Choose "Save the value for later use." If you have an existing group, or are building one manually, go to the Properties of the option group itself and find the Control Source property (it is on the Data tab). The Control Source property should be empty - this indicates that the option group is not bound to a table field.

2. Now add a text box field that is bound to the field to which your option group refers. You can place this text box anywhere on the form because it isn't going to be seen by the user. Its purpose is to hold the translated value from the option group and pass that value to the table field. Go to the Properties of the text box and set the Visible property to No. Make a note of the text box's name (or give it a sensible one yourself).

3. Return to the option group and select its frame (the rectangle surrounding the group). Open the Properties window and make sure you have the correct bit selected. This can be a bit tricky. When the frame is selected its label and all the control buttons inside appear selected too:
   

4. Go to the Data tab of the Properties window and find the After Update property. Click on the property and click the Build button (the one marked with three dots ). Choose Code Builder from the dialog box. This takes you to the form's code module's After Update procedure for the selected frame. All you have to do now is write the code...

It is quite simple to write a VBA code procedure to convert the number that is returned by the option button into a meaningful piece of data for your table.

In the example illustrated above, the option group has buttons for various different Departments. There is a separate Department text box which is bound to the Department field of the form's underlying table. The option buttons are labelled "Design", "Production", "Marketing", "Sales" and "Shipping" but they return the values 1, 2, 3, 4 and 5 respectively when chosen. I need to write some code that runs when the user makes a choice (i.e. when the After Update event is triggered) that says something like: "Look at the value of the group. If it is 1 then write "Design" in the Department text box; if it is 2 then write "Production"... etc."
This sounds long-winded but we can use a VBA Case Statement to make things easy. Here's the code:

Private Sub Frame6_AfterUpdate()
   Select Case Frame6.Value
      Case 1
         Department.Value = "Design"
      Case 2
         Department.Value = "Production"
      Case 3
         Department.Value = "Marketing"
      Case 4
         Department.Value = "Sales"
      Case 5
         Department.Value = "Shipping"
   End Select
End Sub

My option group's frame is called Frame6 (I didn't bother to give it a sensible name) and the text box that was bound to the table field is called Department.

You can change the names, numbers and accompanying values to suit your own example. You can have as many cases as you want but, in practice, if you need a large number of option buttons you would probably be better off using a combo box instead.

When you finish writing the code close the code window, save the form and try it out. If you need to alter your code open the form in Design View and go to View > Code.

Synchronising the Option Group

An option group that is bound to the underlying table automatically displays any value that is already in the field. When you move from record to record different buttons will appear selected, reflecting the data that is already in that field.

When you use an unbound option group there is no direct connection between the group and the field so, unless you do something about it, you will not be able to use the group to read the data that is already there.

Again, the code is fairly straightforward. I have used a case statement to translate the text data from text box (i.e. anything already in the field) back into numbers and feed this information into the frame so that displays the appropriate option button as being selected. It sounds complicated but it is simply the reverse of the previous procedure.

We have to choose an appropriate Event Procedure in which to enter this code. When a form is opened, and when the user moves from record to record the form's On Current event is triggered, so this is the best one to use. The code needs to say something like: "Look at the value in the Department text box. If is is "Design" then set the value of the group to 1; if it is "Production" then set the value of the group to 2... etc." Once again a case statement does the work:

Private Sub Form_Current()
   Select Case Department.Value
      Case "Design"
         Frame6.Value = 1
      Case "Production"
         Frame6.Value = 2
      Case "Marketing"
         Frame6.Value = 3
      Case "Sales"
         Frame6.Value = 4
      Case "Shipping"
         Frame6.Value = 5
      Case Else
         Frame6.Value = Null
   End Select
End Sub

There is one small difference in this procedure. My Case Statement includes a Case Else clause. This is a kind of "catch-all" clause. I have put it there in case someone has manually entered data directly into the table and used a value that is not included in the list in my option group. If that happens the value of the group is set to Null, meaning that nothing is selected.

NOTE: To create an event procedure for the form you will have to open the properties window for the form itself. A quick way to do this to double-click the form selector button in the form design window:

Why not use the code with a bound field?

In theory, you could do without the hidden text box, but the code required would be more complex and synchronisation of the option group with the underlying data would be more difficult to achieve.

Download the File

You can download a fully working demo database illustrating the techniques covered in this tutorial. The files are provided in Access 97 and Access 2000 format, and also as Zip files for faster download (you will need a copy of WinZip or a similar program to extract the zipped files).

OptionGroupsDemo97.mdb	OptionGroupsDemo97.zip	Access 97 format.
OptionGroupsDemo2000.mdb	OptionGroupsDemo2000.zip	Access 2000 format.