Static Variables Part 2: the Advantage of Static Variables

In part one of this tutorial - Using Static Variables - we examined the difference in behaviour between an ordinary local variable and a static variable.  We learnt that whilst both types can only be referenced within the same sub procedure or function in which they were declared, the value of a static variable is retained after code in the sub procedure or function has completed execution.  The terms we used to understand this behaviour were scope and lifetime respectively.

In part two, this current post, we are going to consider how we can use the unique scope and lifetime of a static variable to our advantage.  In order to illustrate this we shall examine the workings of an order counter custom control comprised of a textbox and two control buttons.

An Order Counter Control

This sort of control may be familiar to you if you order your groceries online. It usually appears under an image of a product and allows the customer to set the order amount by clicking the plus and minus buttons without having to type in the actual number in the textbox.  It is of interest to us because it lends itself to the use of a static variable to keep track of the order amount.

The advantage of Static Variables

So what is it about static variables that make them advantageous to use in contexts such as that of our order counter control?  To understand this lets take look at how the VBA code used to power this it is organised.

VBA Code Powering the Order Counter

As we can see, the coding for the form containing the order counter is comprised of five sub-procedures and a general declarations section where three constants, INITIALIZE, INCREMENT, and DECREMENT are declared.  Three of these sub-procedures, Form_Load(), cmdMinus_Click(), and cmdPlus_Click(),  contain event handling code which execute when the form opens, or the minus and plus buttons are clicked, respectively.  Each consist of one statement calling changeOrderAmount(...), the sub-procedure used to calculate the order amount displayed in the txtOrderValue textbox.  This is the location where our static variable, intOrderAmount, is declared.

It is in the changeOrderAmount(...) sub-procedure where the value of intOrderAmount is initialized, incremented or decremented within a SELECT...CASE statement, which is, in turn, based on the value of the intApplyChange argument it received when called. For example, if the  INCREMENT constant was received, the value of intOrderAmount is incremented by 1.  Likewise, DECREMENT decreases the value by 1, and INITIALIZE sets the value to 0. Note the fact that the actual order amount is not set directly when incremented or decremented, but is calculated from its previous value.  For this reason intOrderAmount has to persist across multiple calls to the sub procedure, and is why it is declared as Static.

In order to achieve the above without using a static variable, we could instead declare intOrderAmount in the general declarations section of the form's code module using a Dim  statement or the Private keyword. This gives the variable a module level scope and lifetime: it can now be referenced from any sub-procedure or function within the same module, and any value it contains will be retained while the form connected to the module remains open.  As such, the module level version of intOrderAmount has the same lifetime as our original static version declared within the changeOrderAmount(...) sub-procedure. However, since the variable only needs to be referenced from within the sub-procedure, to give it module level scope in the above mentioned way will leave it unnecessarily exposed to accidental editing as well as making our code less easy to read.

These issues arising from module level scope become more pronounced as more code is added to the module.  Other functions and sub procedures are likely to use their own variables, some of which may also have module level scope. These are generally less easy to keep track of because they can be modified from any location in the module, often under a variety of different conditions. In this sort of context it becomes increasingly difficult to read and understand our code, and  there is a greater chance of accidentally referencing a different variable to the one intended, especially if it has a similar name or performs a similar role. As such, using a static variable with a scope local to the sub-procedure or function in which it is declared, whilst also retaining it value over multiple calls, is the much better option.

Conclusion

The advantage of using static variables lies in the fact that they are, on the one hand,  declared within a sub-procedure or function, and thereby have a local scope; and on the other hand, retain their value once code in the parent sub-procedure or function has completed execution.  As a result, the variable is protected against accidental editing from code elsewhere in the project, whilst retaining its value in order to keep a running total over multiple calls to its parent sub procedure or function.  Furthermore, since static variables are declared within sub-procedures or functions, as opposed to an external location in the project, our code is more manageable, and hence easier to read and understand.  Generally speaking, our code is more streamlined and robust.

Using VBA Static Variables

In this post we are going to look at what static variables are, how they work, and how they are different from ordinary local variables.  In a future post we will build upon this and consider the advantage of  using static variables over locals, and look at an example of how they may be used in practice.

The Difference between Local and Static Variables

So what are static variables exactly? To understand this, let's begin by examining how ordinary local variables work. All variable types have there own particular scope and lifetime.  The first of these determines the locations within the program from which the variable can be accessed, and the latter determines how long the variable is active.  

Local Variables

Local variables have a scope which is limited to the sub procedure or function from which it is declared. For example:

Private Sub testThis()
    Dim intExample As Integer
    intExample = 10
    MsgBox ("The value of our test variable is " & intExample)
End Sub

Here we have declared an integer variable called intExample within a sub procedure called testThis().  Since the variable was declared within the sub procedure, it is local in scope - the value of 10 it  subsequently contains can only be accessed within the testThis() sub after the declaration statement. So if the MsgBox Statement  referencing the intExample variable is used outside the sub, the variable is not recognised. 

Furthermore, once code in the sub procedure completes execution, the intExample variable ceases to exist, and it's value of 10 is lost. The next time the testThis() sub executes, intExample's value will need to be set anew. As such ordinary local variables are limited to the length of time the sub procedure or function is executing. This is an example what is meant by a variables lifetime.

Static Variables

Now let's take a look at how the scope and lifetime of a static variable works in contrast to an ordinary local variable.  Consider the following code snippet:

Private Sub testThis()
    Static intExampleStatic As Integer
    Dim intExampleLocal As Integer
    intExampleStatic = intExampleStatic + 1
    intExampleLocal = intExampleLocal + 1
    response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
        ", whereas the value of intExampleLocal is " & intExampleLocal & ".",                     vbInformation, "Static & Local Variable Output")
End Sub

Here we have created another sub procedure called testThis(), and declared a static variable and an ordinary local variable, intExampleStatic and intExampleLocal respectively.  Notice how the declaration statement for the static variable uses the Static keyword rather than Dim ie: 

Static intExampleStatic As Integer

in contrast to ... 

Dim intExampleLocal As Integer   

Since both variables are declared as Integer, they are automatically initialized with a value of 0.  Then in the following lines of code we increment the value of  each of the variables by 1:

intExampleStatic = intExampleStatic + 1
intExampleLocal = intExampleLocal + 1

... and display both values using a message box statement: 

response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
        ", whereas the value of intExampleLocal is " & intExampleLocal & ".", vbInformation, "Static & Local Variable Output")

Now the scope of both kinds of variable, local and static, work in exactly the same way.  That is to say, static variables, along with ordinary local variables, can only be accessed from within the same sub procedure or function from which they were declared.  Where they differ is in their respective lifetimes.  Whereas a local variable ceases to exist after the code within the same sub or function completes execution, the value contained in a static variable will be retained - at least until the codes'  underlying form or report is closed, or in the case of a global module, until the database itself is closed. To demonstrate this in action, lets see what happens when the testThis() sub procedure starts being called.

The first time testThis() is called the message box output shows that the intExampleStatic and intExampleLocal variables have the same value ie 1:

It is not until the second and subsequent calls that we see the difference in lifetime between static and local variables taking effect.  This is because the value stored in the local variable is lost each time the sub procedure completes, and then re-initialized the next time it is called.  The static variable, on the other hand, retains its value from the previous call.  Hence, the output after the second call shows the static variable to have a value of 2, but the local variable again has a value of 1:

This pattern continues each time the sub procedure is called: the value of the static variable is retained and any increments are added to this retained value, whereas the local variable is created anew and any increments are applied to its initialization value of 0.  Hence the third time testThis() is called, the value of the static variable is increased to 3, and the value of the local variable remains at 1:

... and so on until the sub procedures's underlying form or report is closed.  It is not until the next time the form or report is reopened that the static variable's value is re-initialized to 0.

So this has shown us how static variables work and how they differ from ordinary local variables.  In the next blog post we are going to consider the advantages of using static variables over local variables, and will demonstrate this by creating a a simple order counter control where a user clicks two buttons to increment or decrement the quantity of an item to be ordered without having to type the actual number.

Display a Random Customer Record using VBA and Form Filter

Suppose you are responsible for managing an Access Database containing customer records.  You have been asked to introduce a contact management feature whereby the person operating the database can extract a random customer record.  This blog post walks you through the VBA code needed to generate a random number and use it to filter out a customer record, all at the click of a form button.

In order to demonstrate this we will be using the following data:

Table of Fictitious Customer Records

We will display the random customer record on the following form:

The VBA code starts when the user clicks the "Get Random" button to display a random customer record on the form. Lets begin by creating the control button.

Creating the Control Button

1. Open the form in DESIGN VIEW

2. Click the BUTTON icon in the CONTROLS group on the FORM DESIGN ribbon.

3. Click the location on the form grid where we will be positioning the button.  Resize the control button by clicking on one of the buttons' corner or edges and dragging to enlarge or contract as appropriate.

4. Click the PROPERTY SHEET icon from the TOOLS group of the FORM DESIGN ribbon if it is not already open.

5. Ensure the control button is selected. Click the button to select if it is not. Now the property    sheet is displaying the properties specific to our button.

6. Change the button CAPTION property on the FORMAT tab to "Get Random".

7. Change the button NAME property on the OTHER tab to "btnRnd"

Check how the button looks by changing  to FORM VIEW from the VIEWS group of the FORM DESIGN ribbon. If its OK, we are now ready to add the VBA code which runs when the command button is clicked.

Adding the VBA Code

1. Open the form in DESIGN VIEW, and reopen the PROPERTY SHEET if it isn't already.

2. Select the control button by clicking on it.

3. Select the EVENT TAB on the PROPERTY SHEET, and click inside the grid cell adjacent to where it says ON CLICK.

4. Click the ellipse button ("...") at the end of the cell to open the CHOOSE BUILDER dialog, Select CODE BUILDER from the drop down list and click OK to open the VBA editor.

 

You should now see an empty sub procedure called btnRnd_Click(). This is the control button's event handler.  This means the code we enter here will run when the user clicks the command button at runtime.

 

The VBA to Generate a Random Number

In order to retrieve a random customer record we need to generate a random number which corresponds to one of the customers in the database table. We can do this using an inbuilt VBA function called Rnd.  This generates a random decimal number between 0 and 1. For example, it  may return the value 0.5924582.  For this to be of any use to us, we need change the decimal number to an integer and multiply it by the number of customer records in the table.  So, for example, our table contains 10 customer records so we need to multiply the value returned by rnd by 10 and use another function called int to convert it from decimal to integer. We do this as follows:

    Int(rnd * 10)

This returns a value between 0 and 9.   We now need to add 1 to the value to get a number between 1 and 10:

    Int(rnd * 10)+1

Just one more thing to note: computers are unable to return a true random number on their own.  They return a seed number based on a pre-existing sequence of numbers. However, when we initialize the random number generator with the Randomize statement, the seed number returned is based on the system timer, which effectively makes the returned value a true random number.

So to put all this together we are going to create our own custom function to return a random integer number based on the number of records in our database table. The number returned will correspond to a particular record in the table. We shall call our function getRandom and will pass the number of customer records as the parameter. The code for the function is as follows:

Private Function getRandom(intRecordCount As Integer) As Integer  

    Randomize

    getRandom = Int((intRecordCount * Rnd) + 1)  

End Function

For more information on how custom functions work, please check out my post on Writing Custom Functions for Access VBA.

The next step is to write the VBA code to connect the random number returned by our getRandom function with it's corresponding customer record in the database table.  We shall do this by looping through a DAO Recordset containing records from the customer table.

Looping through the DAO Recordset

Before we can create the code to filter a customer record based on our random number, we need to find a way of mapping any potential number returned by the getRandom function  (in our case this is between 1 and 10) against the records in the database table. Whilst it is tempting to use the returned random number directly against the value stored in the customer_id field in a form filter WHERE clause - ie "customer_id = " & getRandom(intRecordCount) - there is a problem with this.  That is to say, although the customer id's in our table go up in an unbroken sequence from 1 to 10, it is possible that one of these records will be deleted in the future.  If and when this happens, no customer record would be found using the above WHERE clause if the random number corresponded to the deleted record. Moreover, the record count would not include the deleted record so the random number returned would be within a range which did NOT include the last record in the table.

What we must do, therefore, is create a DAO Recordset containing all the records in our customer table and use the value returned by getRandom to step through each customer record in the set until this value has been reached - eg if getRandom returns the value 8, we simply loop though the recordset to the 8th record contained therein.  Once we get to this record we can look up the actual value in the customer_id field, and then use this as the basis of our form filter WHERE clause.

If you are unfamiliar with how DAO recordsets work, you may want to check out my post on Accessing Data with VBA Code - Introducing the DAO Recordset; otherwise, lets jump straight in, and run through the code we will be using.

The first step is to create a new instance of a DAO recordset object and set its value to that of our  tblCustomer table.

Dim rstCustomers As DAO.Recordset

Set rstCustomers = Application.CurrentDb.OpenRecordset("tblCustomer", dbOpenSnapshot)

You may have noticed the second parameter we have used when opening the recordset is dbOpenSnapshot.  This means our recordset will be a static snapshot of the customer table. Unlike the versatile dbOpenDynaset type, records cannot be updated using dbOpenSnapshot, but in cases where editing is not required, the snapshot type is the more efficient option. 

In the next step we are going to count the number of records in the rstCustomers recordset and store the value in an integer variable called intCount.  To count the number of records in rstCustomers we will read the RecordCount property using the following syntax:  rstCustomer.RecordCount.  However, before doing so, it is first necessary to visit all the records in the recordset so they can all be counted. The best way to do this is to use the recordset MoveLast method which moves the recordset cursor though each of the records before pointing to the last record in the set. The syntax for this is rstCustomers.MoveLast. All this is done in three lines of code as follows:

Dim intCount As Integer

rstCustomers.MoveLast

intCount = rstCustomers.RecordCount

Now that we know how many records are in the customer table, we can use our getRandom function, passing intCount as the parameter, to obtain a random number.  We then step through the records in the recordset until we reach this number.  Once this record has been reached, we can read the value contained in the customer_id field.  

To do all this we shall begin by defining an integer variable called intCustomerId.  This will be used to store the customer id once we have arrived at the random customer record.  Before we set up the loop, we need to move the recordset cursor back to the first record using the MoveFirst  method.  This is because the cursor is still at the last record from when did the record count in the section of code above.  We will use a For...Next loop to step through the recordset, starting at the first record and ending at the record which corresponds to the value returned by our getRandom function. This is done with the following line of code: For i = 1 To getRandom(intCount).  The first statement within the loop sets the value of the intCustomerId variable to the value contained in the customer_id field of the record where the recordset cursor is located. We reference the field value using the recordset name ("rstCustomers") followed by an exclamation mark ("!") and then the field name ("Customer_Id") as follows: intCustomerId = rstCustomers!Customer_Id. Once this value is stored in our intCustomer variable we can move the recordset cursor to the next record using the MoveNext method: rstCustomers.MoveNext. Finally the loop counter is incremented by 1 with Next i.  If the end value of the loop counter had been reached before the Next statement had executed, the loop counter will still increment by 1, but the program flow will exit the loop and move to the statement following Next i.  

Dim intCustomerId As Integer

rstCustomers.MoveFirst

For i = 1 To getRandom(intCount)

           intCustomerId = rstCustomers!Customer_Id
           rstCustomers.MoveNext

Next i    

 

So we have stepped through the recordset and arrived at our random record storing the value of it's customer_id field in the intCustomerId variable.  Now the program flow has exited the loop we are going to use the intCustomerId value to display the record on our form.  This will be done by means of a form filter containing a WHERE clause based on the stored customer_id. The syntax for the WHERE clause is "Customer_id = " & intCustomerId and the syntax to create form filter is DoCmd.ApplyFilter filtername, whereclause, controlname.  Although the ApplyFilter method has three parameters, we are just going to use the second - the WHERE clause.  As such we write the full statement as follows:

DoCmd.ApplyFilter , "Customer_id = " & intCustomerId

(NB Although the first parameter is left blank we still need the separating coma just before the WHERE clause string.  The last parameter is also left blank, but a separating coma after the WHERE clause is not needed.)

Lastly we need to close the rstCustomer recordset and set its value to NOTHING.  This clears the memory that it had been taking up and allows us to reopen the recordset again the next time the user clicks the getRandom button. We place this code within a IF conditional statement which checks the recordset is still open before it executes thereby avoiding any error from occurring if it is not.

If Not rstCustomers Is Nothing Then

    rstCustomers.Close: Set rstCustomers = Nothing

End If

All we need to do now is look at how all the code we have run through above is organised in the code module for the frmCustomer form.

As you can see from the screenshot above, our code is divided into two Sub Procedures and one Function, btnRnd_Click(), RandomCustomerRecord() and getRandom() respectively.  When the user clicks the Get Random button on the Customer Form, the code in the btnRnd_Click() sub executes first. This is the event handling sub that Access created automatically when we set up the control button at the start of the tutorial.  There is just one statement here calling the second sub procedure which is RandomCustomerRecord().  This contains the main body of the code for retrieving the random customer record from the database and displaying it on the form.  The reason for giving this it's own sub rather than placing it in the code handler, is that we can potentially reuse this code by calling it from different locations in the program as required.  The only task this code does not do is generate the random number representing a specific customer record for selection.  This code is located in the getRandom() Function which is called from the FOR statement of the FOR...NEXT loop. The number returned from the function determines how many records are stepped through in the rstCustomers recordset before getting to the record which is to be displayed right at the end of the process.

Once RandomCustomerRecord() has the random number and retrieved the customer record from the recordset, the form filter is applied using the value of cutomer id.  The form will then display the record with the same customer_id as that stored in the sub's intCustomerId variable.

And there we have it: a random customer record at the click of a button! 

 

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.

Creating Custom Objects with Class Modules

Suppose we wanted to work with a set of related variables with VBA code.  Let's say these variables relate to a person.  The information we want to store and process may include the person's name, age and gender.   The best way to do this would be to create our own custom person object; with the above-mentioned variables as the object's properties, and any code to process or work with these properties, as the object's methods.

The advantage of creating a custom object is that a lot of related information, and the means to process it, is contained within a referenced entity that is easily accessed and used from different parts of the program.

There are two steps to creating a custom object. The first is to create a blueprint or design for the object which defines what information the object holds and the processing to be done on that information. To do this we create what is known as a Class Module. The second step is to create the actual object based on the class module - this is known as instantiation. Once the class module is in place, we can create as many instances, or objects based on the module, as we need. Hence, we may reuse the code we write in the class module over and over again.

Let's go through the steps to create a class module:

Creating a Class Module

1. Open the database in which the class module is to be created.
2. Click the CLASS MODULE icon in the MACROS & CODE group of the CREATE ribbon.  This opens the VBA editor ready to enter code in our new class module.
   
3. Click SAVE CLASS MODULES in the FILE menu of the VBA editor.
4. Give the new class module the name "clsPerson" when the SAVE AS dialog window opens.

   

   Save As dialog window

Although we haven't started to enter code, the new clsPerson Module shows up in the VBA Project in the CLASS MODULES node of the VBA Project Explorer, and in the MODULES section of the main Access Navigation Pane.

VBA Project Explorer

Access Navigation Pane

Code to Create an Instance of the Class Module

We will add code to our class module in the following section. Before doing so, however, let's look at how the class module is instantiated - ie how the blueprint becomes an actual object.

Objects are instantiated in code outside of the class module itself, where the object is to be used - this may be in the code belonging to an Access form or report, or perhaps in a conventional module.

There are two things we need to do. Firstly we define a variable through which the object can be referenced. We do this as follows:

Dim objPerson As clsPerson

Here objPerson is the name of the new object, and clsPerson defines the object's type - ie the Class Module on which it is based.

Secondly, we instantiate the object with the New keyword; and link the object to the variable that we created above with the Set keyword. This is done as follows:

Set objPerson = New clsPerson

However, before we can create an instance of the class we must first add our code to the class module. Let's start by writing the code to create the class module properties.

Setting and Getting Class Module Property Values

The person object we are creating has three properties - name, age and gender.  Class properties are basically variables with CLASS MODULE level scope.  As such, they are declared pretty much the same way as variables in the declarations section of the CLASS MODULE, except we use the PRIVATE keyword instead of DIM.  As such, this is the code we need to write:

Private strName As String
Private intAge As Integer
Private strGender As String

By restricting scope to CLASS MODULE level (as opposed to GLOBAL), the data stored in each variable cannot be accessed directly from outside the class module. This means we must set up a special sort of interface within the class module so we may get and set our object property values after the object has been instantiated.  We do this through PROPERTY LET and PROPERTY GET.  These are located in the main section of the class module code with it's SUB's and FUNCTIONS. Here are the blocks of code to set and get the NAME property stored in the strName class module variable.

Property Let Name(strNameIn As String)
   strName = strNameIn
End Property

Property Get Name() As String
   Name = strName
End Property

Let's examine how these work.  Imagine we have created an instance of the clsPerson class and called it objPerson.  To set the Name property of the object to, let's say, "Sarah",  we would use the following code:

objPerson.Name = "Sarah"

Doing this invokes PROPERTY LET NAME in the clsPerson class module code (see code snippet above).  The string value "Sarah" is received as the strNameIn argument, and is then assigned to the strName class module variable that we set up in the declarations section above.  As such,  PROPERTY LET has set the value of a private class module variable from a location external to the class module itself.

Then to retrieve the value of the objects Name property from outside the class module, we just need to reference the object name (eg objPerson) and property (eg Name) in the same way.  For example, to retrieve the property value and store it in a variable called strGetName we would use the following code:

strGetName = objPerson.Name

Doing this invokes PROPERTY GET NAME in the clsPerson class module (see code snippet above).  PROPERTY GET returns a returns the value of the property by assigning a value to NAME.  As such, "Name = strName" assigns the value stored in the strName class module variable to the objects Name property and returns this value whenever the object's Name property is referenced outside of the class module.  It works in a similar way to calling a VBA FUNCTION.

The Age and Gender properties in the clsPerson class are set and retrieved in the same way.  The screenshot below shows all the class module code to set and get the clsPerson properties:

Class module code for clsPerson

Adding SUB's and FUNCTION's to the Class Module

Class modules and objects are not just about storing and retrieving property values.  As written above, we can also process and work with those property values in all kinds of ways.  To do this, we add SUBs and FUNCTIONs to the class module.  These are referred to as class methods, and providing they have been created using the PUBLIC keyword (as opposed to PRIVATE), they can be called from outside the class module after the object has been instantiated.

We are going to create a simple method to categorize a person based on the information stored in the object properties.  When the method is called after instantiation, it will return one of eight categories which describe the person stored in our object.  These are:

1. Male under 18
2. Female under 18
3. Male 18 to 30
4. Female 18 to 30
5. Male 31 to 64
6. Female 31 to 64
7. Male 65 and over
8. Female 65 and over

To do this we shall write a  PUBLIC FUNCTION called categorizePerson. Here is the code we shall use:

Public Function categorizePerson()

On Error GoTo myError 

    Dim strAgeCat As String  
    If intAge < 18 Then
        strAgeCat = "under 18"
    ElseIf intAge > 18 And intAge <= 30 Then
        strAgeCat = "18 to 30"
    ElseIf intAge > 30 And intAge <= 65 Then
        strAgeCat = "31 to 64"
    ElseIf intAge >= 65 Then
        strAgeCat = "65 and over"
    End If
    categorizePerson = strGender & " " & strAgeCat  
leave:
    Exit Function  
myError:
    MsgBox Error$
    Resume leave  
End Function

As we can see, the method uses IF .. THEN .. ELSEIF statements to select an age category based on the value of the age property stored in the intAge class module variable. The person's gender is then added to the description (thereby doubling the number of potential categories) and returned to the calling code.

The code to invoke the method after the object has been instantiated is as follows:

objPerson.categorizePerson()

Testing the Custom Module

In order to demonstrate how all this works in practice, I have created an unbound Access form to test our clsPerson class.  The form allows us to enter the three property values used by clsPerson, and create a new instance of the class containing these values when the SET PERSON PROPERTIES control button is clicked.

After clicking SET PERSON PROPERTIES the btnSet_Click event (shown in the screenshot below) is invoked. It's code creates a new object instance called objPerson using the SET and NEW keywords. The values contained in the unbound textboxes are then referenced and set as the respective Name, Age and Gender properties for the object. Note how the objPerson variable is declared in the declarations section of the form module so it will persist as long as the form is still open or its value is reset to NOTHING when the DELETE PERSON button is clicked thereby invoking the btnDeleteObj_Click event.

Once the object has been created, the btnSet button is disabled through the code logic in the setControls sub (see screenshot above). This also enables the other three buttons which let us retrieve the property values, categorize the person, or delete the object.

Clicking the GET PERSON PERSON PROPERTIES button invokes the btnGet_Click event where the property values are retrieved, added to a string variable, and displayed in the OUTPUT textbox (see screenshot below).

Finally, clicking the CATEGORIZE PERSON button invokes code in the btnCat_Click event and the category is displayed in the OUTPUT textbox (see below)

As such, we can see how the properties and methods in clsPerson (ie PROPERTY LET, PROPERTY GET, and CATEGORIZE PERSON) are invoked with statements such as objPerson.categorisePerson after the object has been instantiated in the TEST CUSTOM PERSON OBJECT form.  I hope this gives you a good example of how VBA class modules and custom objects work in practice.