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.

Using Custom Functions in Calculated Controls

Back in May 2012, I wrote a blog article on Writing Custom Functions for Access VBA.  Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers.  We do this by creating a public function with the VBA programming language and save it inside a global module within the database.

The blog I wrote in 2012 explained how these custom functions can be accessed from sub procedures associated with forms and reports elsewhere in the database.  Today, however, I am going to explain how custom functions can be utilised in calculated form controls, without needing to invoke them with VBA itself.

Let's begin by looking at the custom function we are going to use.

Custom Function to get Week Commencing

I have created a custom function called getWeekCommencing to calculate the week commencing date of a given week, in a given year.  For example, the week commencing date of the 14th week of 2019 is 1st April 2019 (if we count Monday as the first day of the week). As such the function takes two arguments - week number and year.  The full syntax for using the function is as follows:

getWeekCommencing(weekNumber, Year)

The code I used to create this function is as follows:

Public Function getWeekCommencing(intWeekNo, intYear)
On Error GoTo myError
    Dim intJan1 As Integer
    Dim varStartDate As Variant  
    intJan1 = Weekday(DateSerial(intYear, 1, 1), vbMonday)
    varStartDate = DateAdd("d", -(intJan1 - 1), DateSerial(intYear, 1, 1))
    getWeekCommencing = DateAdd("ww", intWeekNo - 1, varStartDate)  
leave:
    Exit Function
myError:
    MsgBox Error$
    Resume leave  
End Function

If you are unfamiliar with VBA you can still copy this code into your database and use it within a calculated control. There are instructions for doing this below:

Instructions for adding the function to your database.

1.  Copy the code above onto your clipboard.
2.  Open the database you are going to add the function to.
3.  Select the CREATE ribbon.

   

   Above: The MODULE icon is located on the right-hand side of the CREATE ribbon.

   
   
4. Click the MODULE icon from the MACROS AND CODE group.  This opens the VBA editor and creates a new module ready for the code to be added:
5. Paste the code into the VBA editor as in the screenshot below:

Above: The VBA Editor:

You may notice that I have changed the default name of the Module (see the PROJECT EXPLORER in the 

left-hand side bar) from Module 3 to "myFunctions".  You can do the same by highlighting the module name in the 

project explorer and hitting the F4 button to bring up the PROPERTIES window. You may then change the name property

 to one of your choice.  However, this is not essential.

The custom function is now ready to use in a calculated form control. 

Setting up the Test Data for this Exercise

Before we create the form to test the function, we first need to set up a simple database table containing test data. The table needs just two fields: WeekOfYear and Year, both of which are of the NUMBER data type.

Above: tblWeeks in Design View

Above: tblWeeks in Form View with test data entered.

Once the table is set up, we can add our test data as I have done in the screenshot above. Although there are  52 week in the year, the first week may not start on a Monday, so the data entered in WeekOfYear field can be anything between 1 and 53 (so the possibility of a partial first week is offset by Week 53).

The Calculated Control

Now we have added the getWeekCommencing function along with test data to our database, we can create the form containing the calculated control.

Above: The Week Commencing Calculator form in Design View.

To do this we need to create a form bound to the tblWeeks table.  We then need to add three text boxes:  the first text box (txtWeekOfYear) is bound to the WeekOfYear field in tblWeeks, and the second (txtYear) is bound to the Year field.  The third text box (txtWeekCommencing) is unbound.  This is where we are going to add our calculated control which returns the week commencing date (based on the data displayed in the WeekOfYear and Year textboxes).

Here are the instructions for creating the calculated control:

1. Create the third text box that we are going to use for the calculated control as in the screenshot above. 
2. Display the PROPERTY SHEET if it is not already visibile.  The PROPERTY SHEET icon is located in the TOOLS group of the DESIGN ribbon.
3. Select the new text box by clicking on it.
4. Click the OTHER tab of the PROPERTY SHEET, and change the NAME property to txtWeekCommencing. (I also suggest you change the names of the first textbox to txtWeekOfYear and the second to txtYear by selecting them and changing the NAME properties).
5. Click the DATA tab on the PROPERTY SHEET (having first selected txtWeekCommencing).  We are now going to enter an expression which uses our getWeekCommencing custom function.  
6.  Enter the expression below directly into the CONTROL SOURCE property:

=getWeekCommencing([txtWeekNumber],[txtYear])

The txtWeekNumber parameter references the value in the txtWeekNumber textbox, and the txtYear parameter does the same for the txtYear text box.  

In this exercise, the expression was entered directly into the CONTROL SOURCE

property.  Alternatively, we could have clicked the elipse button at the end of the CONTROL SOURCE
property row to open the EXPRESSION BUILDER.  Using the Expresison Builder is, however, something 

I would need to cover another day, in another post!

And that's it! All you need to do now is save the form and open it in FORM VIEW.  Use the form's navigation buttons to view each record in the tblWeeks table.  The calculated control uses the getWeekCommencing custom function to processes the week number and year data for the current tblWeeks record, and displays the appropriate date.

NB - Just to clarify, the format for the week commencing date in this screenshot is for the UK (ie dd/mm/yyyy). 

The same date using the US format would be 01/07/2019 (mm/dd/yyyy). However, your computer should be set up
to display the date format appropriate for your location automatically.

Changing "Entry Key Behaviour" to Allow New Lines within a Textbox

In my previous blog post, I wrote about Adding Rich Text Formatting to MS Access Data.  However, there was one related topic which I didn't cover - ie how to change the entry key behaviour to allow new lines within a text box.  This is done by changing the ENTRY KEY BEHAVIOR property of the text box in question.

The ENTRY KEY BEHAVIOR property set to 

NEW LINE IN FIELD for the txtDescription text box.

Let's imagine we have a text box on a form in which we are going enter a large amount of text data separated by paragraphs. We have set the field in the underlying table to accept LONG TEXT data, and perhaps also enabled rich text formatting.  The first thing we do is resize the text box in FORM DESIGN view by selecting the text box and dragging the lower right corner so it is large enough to display a good area of text without having to constantly scroll down.

If we left the ENTRY KEY BEHAVIOR property at its default setting, it would not be possible for a user to create a new paragraph within the text box whenever they hit the ENTER key.  What we need to do, therefore, is change the property value from DEFAULT to NEW LINE IN FIELD. This is how:

1. Open the form in DESIGN VIEW.
2. Select the text box control to be modified by clicking it.
3. Open the PROPERTY SHEET by clicking the icon in the TOOLS group of the DESIGN ribbon (if it's not already open).
4. Click the OTHER tab on the property sheet.
5. Locate the ENTRY KEY BEHAVIOR property and change the value to NEW LINE IN FIELD.

Once the property has been changed, the user can enter text in the text box and use the ENTER key to create a new paragraph without the focus shifting to the next control in the tab order.

Above: Example of a text box with the ENTRY KEY BEHAVIOR property modified to

  NEW LINE IN FIELD.

Enable and Disable a Form Control using VBA

Earlier this week I was working on a design for an Order Management Database, and one of the tasks I dealt with involved dynamically Enabling or Disabling one of the form's Command Buttons' using VBA.  This gave me the idea for the present Access tip.

The command button was located on a Customer Details form which had an Orders Subform in the lower section.  The reason I wanted to Enable or Disable the Command Button (located on the parent form), was that it was used to Delete the selected record highlighted in the Orders Subform's datasheet. I needed to get the Delete Button to enforce the business rule whereby once an order has been confirmed, it should not be deleted. As such, if the user highlighted a confirmed order in the subform, the Delete button is dynamically disabled, and vice versa.

To do this I made use of the Order Subforms ON CURRENT event.  This event is triggered whenever the focus moves from one record to another, or when the first record receives the focus as the form opens.  As such, if a user selects a record in the subform datasheet by clicking on one of the rows, the subform's ON CURRENT event fires.  This is the code I wrote to determine whether the Delete button should be Enabled or Disabled.

If IsNull(DLookup("OrderConfirmed", "tblOrders", "OrderId = " & Me!OrderId)) = False Then

        Forms!frmcustomer!ctlDeleteOrder.Enabled = False

Else

        Forms!frmcustomer!ctlDeleteOrder.Enabled = True

End If

As you can see, I have used an If ... Then ... Else Statement to determine whether or not the Order has been confirmed.  In order to create the conditional expression, I used the IsNull and DLookUp functions together to see if  the OrderConfirmed field of tblOrders contained a date. The IsNull function returns a boolean value, True or False, to indicate whether its parameter (in this case the result of a DLookUp function) is or is not null;  and the DLookUp function, looks up the value contained in the OrderConfirmed field of tblOrders where OrderId matches that of the current record displayed on the Orders subform.  

Since the presence of a date in the OrderConfirmed field indicates that the order has been confirmed, the IsNull function returning the value of FALSE (remember this is a double negative!), tells us the order has indeed been confirmed, and vice versa.   As such, when the condition of the first line of the If Statement is False, the Delete Command Button on the main form should be disabled.  This is done by referencing the Delete Command Button's ENABLED property, and setting it to FALSE:

 Forms!frmcustomer!ctlDeleteOrder.Enabled = False

And if the result of the If ... Then ... Else Statement had returned TRUE, the Delete Command Button is Enabled by setting it's ENABLED property to TRUE:

Forms!frmcustomer!ctlDeleteOrder.Enabled = True

Using The Tab Control for Tidy Form Presentation

The Tab Control is a type of form control used to aid presentation and enhance user-friendliness.  It works much the same way as the Access Ribbon - the user clicks a tab to reveal a new strip.  However, instead of Ribbon icons, these strips contain our text boxes or other form control that we choose.  As such, it allows us to separate the fields on our forms, displaying one group at a time. Here is a screen shot of one I prepared earlier:

Figure 1a: The Tab Control: Tab 1 "Personal".

Figure 1b: The Tab Control: Tab 2 "Address". 

Figure 1c: The Tab Control: Tab 3 "Education".

As you can see, the Employee Record Form in figure 1 above contains a Tab Control which is comprised of three separate Tabs.  The first tab shown in figure 1a has been given the caption, "Personal", and displays fields relating to the Employee's ID, Name and Date of Birth. The second tab in 1b relates to the employee's Address, and the third in 1c relates to Education.  As such, we see how the Tab Control has enabled us to organize the information into logical categories, in addition to presenting that information in a tidy and easy to access format.  In fact,we have condensed 11 fields to fit on a small Dialog form.

Lets take a look at how the form from figure 1 was created. 

Adding a Tab Control

1. To begin with I created a new form by clicking the FORM DESIGN icon.  This is located in the FORMS group of the CREATE ribbon.
2. I set the RECORD SOURCE property (via the DATA tab of the PROPERTY SHEET) to the appropriate table.
3. I clicked the TAB CONTROL icon  (located in the CONTROLS group of the DESIGN ribbon), and clicked a position for it to go on the form design grid. A blank Tab Control with two pages appears on the grid.

   

   Figure 2: A blank Tab Control added to the
    Form Design Grid.

Customizing the Tab Control

The  process of customizing the Tab Control simply involved adding an extra tab page and giving each tab its own individual name - ie Personal, Address and Education.  

1. I began by selecting the Tab Control by clicking its outer edge so that it was highlighted. 
2. Then I clicked the INSERT PAGE icon  (located in the CONTROLS group of the DESIGN ribbon).  This added a new blank page to the tab control, thereby giving us the three required tabs.
3. Next I clicked the part of the Tab Control which says PAGE1 to select this particular tab page. 
4. I then changed the CAPTION property for PAGE1 to "'Personal".  This property is located on the FORMAT TAB of the PROPERTY SHEET.
5. I repeated the process for the other two tab pages -  so I had a tabs for  "Personal", "Address" and "Education" respectively.

Adding Text boxes to the Tab Control

The final stage involved adding the text boxes to the tab control.  The key thing to remember here is that it is important to select the required tab page before adding the text box to the tab control.  It is also worth mentioning that if you are moving an existing form control to the tab control, you will need to cut and paste rather than simply drag and drop (otherwise it will sit on top of the tab control and not be a part of it).

1. I began by clicking the ADD EXISTING FIELDS icon (located in the TOOLS group of the DESIGN RIBBON).  This opened the FIELD LIST pane.
2. I then selected the Personal Tab of the tab control (like I did in stage three of the customization process above).  
3. I then dragged the first four fields - EmployeeId, Firstname, Surname and DOB - from the pane over to the tab control and positioned them in the required location. When you try this yourself, notice how the tab control goes black as you position the text box. This shows that it is being added to the Tab Control rather than the form Detail.
4. I repeated stages 2 and 3, adding the Address and Education related fields to the Address and Education tabs respectively.  
5. All I had to do then was align and resize the text boxes for a professional finish.  A good way of doing this is to click on the text box and manually enter the grid position on the FORMAT tab of the PROPERTY SHEET - the TOP property determines the vertical grid position, the LEFT property the horizontal, and the WIDTH is self explanatory. You can also position the text box label's separately using the same method. 

Figure 3: Adding fields to the Personal Tab Page.