Programming Access Option Groups with VBA

In my previous post on How to Use Option Buttons, we learnt how to set up an Option Button Group bound to a field in the form's underlying database table.  We learnt that an Option Button Group is comprised of two separate types of control - the Option Group control itself, and the individual Option Buttons within it. Once set up, these function together as a single unit.  

Today we are going to learn how to set up a similar Option Button Group, but this time, we are going to use VBA to determine which button has been selected, and change the behaviour of the form based upon the selection. 

Screen Colour Option Group

To do this we are going to set up an Option Group containing three Option Buttons (see screenshot above).  When the user clicks one of the buttons in the Option Group,  VBA subprocedures are called, and the background colour of the form changes in accordance with the users' selection. Here is a video of how it works in practice.

The process for setting up the form with the Option Group is very similar to that which we went through in my last blog post on How to Use Option Buttons.  The main difference is that previously we bound the data selected in the Option Group to a field in the form's underlying database table.  This time, however, we are going to leave the Option Group unbound, and reference the data selected in the Option Group with VBA code.  To do this we will need to make use of the Option Groups On Click event to trigger the subprocedure containing that code when the user clicks any button in the Option Group.

Setting up the Form and Option Group

Here is the procedure for setting up the Option Group:

1) Create a new form by clicking the FORM DESIGN icon on the CREATE ribbon.
2) Go to the CONTROLS section of the DESIGN ribbon and drag an OPTION GROUP control down onto the design grid. 

3) Open the PROPERTY SHEET while the OPTION GROUP CONTROL is highlighted and enter the name "opGrp" in the NAME property (under the OTHER tab)

4) Next go back to the CONTROLS section and drag an OPTION BUTTON control onto the OPTION GROUP which we placed on the grid in the previous step.

5) Enter the name "optRed" in OPTION BUTTON's NAME property whilst the control is highlighted.

6) Repeat steps 4 and 5 twice, creating two more  OPTION BUTTONS called "optGreen" and "optBlue" respectively.

Design View of our Option Group containing the three Option Buttons - optRed, optGreen and optBlue

The next step in the process is to check the OPTION VALUE property is set up correctly for each of the buttons.  To do this you will need to select each Option Button in turn, and go to the property sheet DATA tab. The values are set automatically when you drag the Option Button controls into the Option Group, but just to be sure, check the value for optRed is "1", optGreen is "2" and optBlue is "3".

The Option Value property for optRed.

As you may recall from my last blog post, the Option Button selected by the user at runtime determines the Value of the Option Button Group as a whole. As such, if the user selects optGreen, the value of the group is "2" etc.  It is this value that our VBA code needs to reference when the subprocedure runs.

Programming the Option Group

Let us now turn our attention to the VBA code which executes when the user clicks one of the Option Buttons at runtime. In order for our code to detect when any button within the Option Button Group is selected, we need to make use of the Option Group's ON CLICK event.

This is done as follows:

1) Select the Option Group Control (which we have called "optGrp") on the design grid so it is highlighted orange.

2) Go to the PROPERTY SHEET and select the EVENTS tab.

3) Click the ellipse button ("...") at the end of the ON CLICK property row to open the VBA editor.

Selecting the ON CLICK event for the "optGrp" OPTION GROUP.  Clicking the 
ellipse button ("...") opens the VBA editor.

After clicking the ellipse button on the ON CLICK row of the PROPERTY SHEET, the VBA editor will have opened and automatically created a sub procedure called "optGrp_Click".  This is the ON CLICK event-handler. Any code we enter here will be executed when the user clicks any button in the option group at runtime. 

Our first programming task is to determine which Option Button the user clicked.  This is done by referencing the Option Groups VALUE property as follows:

formname!optiongroupname.Value

In our case the code will be:

Me!opGrp.Value

"Me" is a quick and simple way to reference the form name within which the code module is contained;  "opGrp" is the name of our Option Group; and Value is a reference to the value property of the Option Group as a whole.  Note the form name and control name is separated by the "!" character, and the control name and property is separated by a full stop ".". 

Referencing the OPTION GROUPS VALUE property in this way gives us the OPTION VALUE of the button selected by the user.  As you may recall the OPTION VALUE for the red button was "1", green was "2" and blue was "3".  So if the user clicked the green button, the above statement will return "2" when it executes at runtime.

In order to use the OPTION GROUPS VALUE property to change the background colour of the form, we are going to do two things. Firstly we are going to call a separate VBA sub procedure from the OPTION GROUPS ON CLICK event-handler, passing the VALUE property as the parameter; secondly, we are going to create this subprocedure which will process the VALUE property and then change the background colour of the form. 

The event-handler code is as follows:

Private Sub opGrp_Click()

    Call setBackgroundColour(Me!opGrp.Value)

End Sub

As we learnt above, the first and last line of this event handler is created automatically when we clicked the ellipse button ("...") in the ON CLICK row of the Option Groups property sheet.  All we have done is add the middle line to call a sub procedure called setBackgroundColour (yet to be created), and pass the referenced value of the Option Group as the parameter.

Let us take a look at the code for our setBackgroundColour sub procedure:

Private Sub setBackgroundColour(intOpValue)

    Dim lngRed As Long, lngGreen As Long, lngBlue As Long

    lngRed = RGB(255, 230, 230)

    lngGreen = RGB(242, 255, 230)

    lngBlue = RGB(230, 240, 255)

    

    Select Case intOpValue

    

        Case 1

            Me.Detail.BackColor = lngRed

            

        Case 2

            Me.Detail.BackColor = lngGreen

            

        Case 3

            Me.Detail.BackColor = lngBlue

            

    End Select

    

End Sub 

Here is a breakdown of how the code works: 

1) The first line of the sub procedure receives the option value argument (intOpValue) which was passed to it from the calling statement we wrote in the ON CLICK event handler.

2) The next section sets up three variables of the LONG data type and assigns each one a colour value which is used to set the forms BackColor property to red, green or blue as required.

3) Next we have set up a SELECT CASE statement to determine the value of the intOpValue variable which, as we have learnt above, contains the OPTION VALUE of the OPTION BUTTON clicked by the user at runtime.

4) Then each respective CASE statement tests whether it's value matches intOpValue.  When it comes to one that does, the BackColor property of form is set accordingly. So if the value of intOpValue is "2", the second Case statement invokes ...  Me.Detail.BackColor = lngGreen ... to set the form's background colour to green.

There is just one more thing we need to do before we can see our form and code in action. This involves calling the setBackgroundColour sub procedure as soon as the form has opened.  This is so the form's background colour is set in accordance with the default option button selection in the first instance.

To do this we are going to make use of the forms ON CURRENT event which fires just after the form opens.  The procedure is similar to how we set up the ON CLICK event-handler for the Option Group.  This time we need to select the form in the design grid by clicking the square at the top right and then open the property sheet for the FORM.

Selecting the FORM in design view.

 

Then we need to open the EVENT tab (of the FORMS property sheet) and click the ellipse ("...") button at the end of the ON CURRENT row.  This creates the event-handler in the VBA editor.  Once that has been created we just need to enter the same code as previously to call the setBackgroundColour sub procedure as follows:

Private Sub Form_Current()

    Call setBackgroundColour(Me!opGrp.Value)  

End Sub

That's it!  Our form with the programmed option group is now ready to open and use.

How to Use Option Buttons (aka Radio Buttons or Option Groups)

 What are Option Buttons?

So what are Option Buttons, and what are they used for? Option Buttons are a user-friendly form control that enables users to select a single value from a group of given options.  This is done by clicking one of a number of boxes, with each box representing one of the available options.  When the user clicks a box to select the value, any previously selected boxes are unselected as a result.  In this way, only one value can be selected at any one time.  

Example of an Option Button Group.

For example, in the screenshot above, there are three options for the user to select one out of a possible three teams - team 1, team 2 and team 3.  The advantage of using an option button group over a combo box with the same available list of values is that it is quick and simple for the user to enter data.  It is also preferable to a textbox in so far as it limits the value entered to one in the group of options.  On the downside, an option button group tends to take up more space of the form, particularly if there are many options to choose from.  If this is the case, a combo box with data entry restricted to items in the list, maybe a better choice.

In the following exercise, we are going to re-create the Option Button Group from the above screenshot.  To begin with, let take a look at the underlying database table.

The underlying database table containing the "team" field 
for the Option Group's Control Source.

As we can see, the underlying table contains four fields - ID, firstName, surname and team.  It is the latter field, team, which is going to be the Control Source for our Option Button Group. Before we start the exercise it is worth mentioning that the Option Button Group is actually comprised of two separate types of control: first, we have the Option Group control, and then we have the individual Option Button controls which are contained within the Option Group.  So to re-create the Option Button Group in the exercise below, we will need to use 1 Option Group control, and 3 Option Buttons, four separate controls acting together as one.  It is the Option Group control that contains the Control Source property for the group as a whole, whilst the Option Buttons each have their own individual Option Value properties.

How to Create an Option Button Group

1. Open the form in DESIGN VIEW.
2. Select the OPTION GROUP control from the  DESIGN RIBBON
   
   and drag it down onto the form design grid.
3. Whilst the OPTION GROUP control is still selected in the design grid, open or go to the PROPERTIES window, select the DATA tab, and then select team from the CONTROL SOURCE drop-down list (NB this assumes you have already set the CONTROL SOURCE of the form itself to the underlying table).

   

   Form design grid with the option group control 
   added (highlighted orange).  The property sheet is also visible
   for the control with team entered as the control source.

   
   
4. Next, select the OPTION BUTTON control from the DESIGN RIBBON, and drag it down onto the OPTION GROUP which you added to the design grid in step 2.  You should notice the OPTION GROUP turn black once the OPTION BUTTON is over the control and ready to drop.  This indicates that the button will be added to the group.
5. Whilst the OPTION BUTTON is still selected, open and/or go to the PROPERTY WINDOW, select the DATA TAB, and check the OPTION VALUE property.  It should say "1".  This is the value that will be bound to the team field if the button is selected by the user at runtime.
6. Repeat steps 4 and 5 for the remaining two CONTROL BUTTONS checking the OPTION VALUE properties say "2" and "3" respectively.

   
   

   Design grid showing three option buttons added to the
   option group control.  The property sheet for the third control button
   (showing the option value property) is also visible.

The form should now be ready to open in FORM VIEW.  The screenshot below shows the finished form with the underlying table.  I have added some data to demonstrate how information contained in the Option Button Group is stored in the database.

Form with option button group.  Select an option button on the form results in the option value property being stored in the underlying database table.

Note how team 3 is selected for the Sarah Arden record (ID 7) in the option button group.  As you may remember, the option value property was set to "3" for the last button.  This is the value that has been stored in the underlying database table.

This post has shown how we create an option button group using a combination of an option group control and three option buttons.  I have demonstrated how the control source for the group as a whole is bound to a field in the forms underlying database table, and how each option button has a unique option value property which is the value stored if the user selects a given button at runtime.  

In a future post, I intend to show how option button groups can be used to control the behaviour of a form at runtime, rather than being bound to a field in an underlying database table. To do this I will demonstrate how we can reference the option button group with VISUAL BASIC code, and respond to its click events.