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.

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.

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.