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
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
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...
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.
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).
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:
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
). 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
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
Department.Value = "Design"
Department.Value = "Production"
Department.Value = "Marketing"
Department.Value = "Sales"
Department.Value = "Shipping"
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.
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
Frame6.Value = 1
Frame6.Value = 2
Frame6.Value = 3
Frame6.Value = 4
Frame6.Value = 5
Frame6.Value = Null
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
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
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.
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).