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.

Linking Access to an Excel Worksheet

Excel is great for number crunching, and can even be used as a basic database to store information.  However, when it comes to displaying and presenting information, the MS Access database has some big advantages over it's peer in the Office 365 suite of software. The reason for this is that Access has a number of tools and features specifically designed to make data easier to read and understand - the Reports facility being just one.

The good news is that Access has the ability to connect to, and work with, Excel data in two different ways.  Firstly, Access can import data from an Excel worksheet into an Access table.  This method lets us work with a "snapshot" of data from Excel as it was at the time the data was extracted (there is more information about importing from Excel in my post on "Importing and Exporting Data between Access and Excel").  The second method is to set up a "live" link between the Excel worksheet and the Access table. This has the advantage of giving us direct access to current data in the spreadsheet; so any updates, additions or deletions will show up in Access (after data is refreshed/reloaded) as soon as the data is saved in Excel. However, please do note that this connection is read-only; new data cannot be saved back into the Excel spreadsheet without specifically doing an additional export.

Today we are going to learn how to set up this "live" link. To do so, I have set up a sample Excel worksheet with fictitious personnel records.

Linking to Excel

1. Create a new Access Database, or open an existing one.
2. Select the EXTERNAL DATA ribbon.
3. Click the NEW DATA SOURCE icon in the IMPORT AND LINK group.
   

   

   Above: Adding a New Data Source from the External Data ribbon.

4. Select FROM FILE from the drop-down menu, and then EXCEL from the sub-menu.  This opens the GET EXTERNAL DATA - EXCEL SPREADSHEET dialog form.
   

   

   Above: The Get External Data - Excel Spreadsheet dialog form.

5. Click the BROWSE button and select the Excel spreadsheet you want to link to.
6. Click the LINK TO A DATA SOURCE BY CREATING A LINKED TABLE radio button, and then click OK.  
7. The next page of the wizard opens where you should see a sample of the worksheet you are linking to.  If your worksheet had column headings, ensure the FIRST ROW CONTAINS COLUMN HEADINGS box is ticked, and click NEXT. (Please note that if your spreadsheet has more than one worksheet or named range, you will see an additional wizard page asking you to select the specific worksheet or range before the page in the screenshot below).
   

   

   Above: First page of the Link Spreadsheet Wizard.

8. Enter the name you are going to call the linked Access table in the last page of the wizard, and click FINISH.

Once you have done this, you will see the new linked table appear in the left-hand Access navigation pane under TABLES.  Note how the linked table icon is a blue arrow pointing to the Excel logo, thereby indicating the table is linked to an Excel spreadsheet.

Above: The table linked to Excel.

Now the link has been established to the Excel worksheet, we are free to base forms, reports and queries on the linked table as if it is native to Access itself.

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.

Converting Minutes to Hours:Minutes in MS Access

There may be occasions when we need to convert the difference between a start time and an end time, and display the result in hours and minutes.  For example, suppose we set up an Access table field to record the time a person starts work (the session_start field) and another to record when s/he finishes (the session_end) field.  Rather than set up a third field to store the total time worked, we can instead calculate this value automatically using a calculated control in an Access form, or a calculated field in an Access query.

Above: the bottom text box (txtHoursWorked) is a calculated control. 
The CONTROL SOURCE is an expression based on the values contained in the top two text boxes
(bound to the session_start and session_end fields in the undelying table).

However, to do this we must use two expressions to calculate the time difference, and then display the time difference in the hrs:mm format.  So if we enter 20:40 as the session_start, and 22:06 for session_end, we need an expression to calculate the total minutes worked (which in this case is 86 minutes), and then convert the total minutes to hours/minutes format (which would be 01:26).  To achieve this we use the DateDiff and FormatDateTime functions.  Lets take a look at each function individually before combing them to produce the required result.

The syntax for the DateDiff function is as follows:

DateDiff("interval", time_1, time_2)

The function works by calculating the time difference between the time_1 and time_2 parameters, and returns the result in accordance with the interval parameter.  In our case, we want need the result returned as minutes, so enter "n" as interval.   Time_1 and Time_2 correspond to the start time and end time.  To get these values for the expression, we can we put a reference to the values contained in the text boxes for "Start time" and "End time".  In our example these text boxes are called txtStartTime and txtEndTime.  This what our DateDiff function looks once we add the parameter values:

DateDiff("n", txtStartTime, txtEndTime)

The value returned in our example is 86 minutes.

The second function that we are going to use is FormatDateTime which converts the value returned by the DateDiff function into hours and minutes.  The syntax we need to use in the FormatDateTime is as follows:

FormatDateTime( (minutes/(24*60) , named_format)

This first parameter is a division of the number of minutes (returned in the DiffFunction) by the total number of minutes in a day -  which in our example is 86/(24*60) - and returns the result in accordance with the value contained in the named_format parameter.  The latter is a numeric value indicating the required time format - which in our example is vbShortTime ie hours:minutes.  The numeric value we enter for the vbShortTime format is 4.  This is what our FormatDateTime function looks like once we add the parameter values:

FormatDateTime((86/(24*60) , 4)

The value returned from this expression is 01:26 - ie 1 hour and 26 minutes.

When we come to enter the actual expression used as the control source for the TOTAL (HRS:MINS) text box (referred to as txtHoursWorked in the screenshot below), we need to combine these two functions by nesting the DateDiff function inside the first parameter of the FormatDateTime function. This is done as follows:

FormatDateTime((DateDiff("n", txtStartTime, txtEndTime/(24*60) , 4)

Finally, we just need to enter this expression into the CONTROL SOURCE property of the
txtHoursWorked text box (see screen shot below):

The txtHoursWorked text box is highlighted on the left, and the expression used as the CONTROL SOURCE property value is highlighted on the right.

Creating a Custom Save Button for an MS Access Form.

When we use an MS Access form to create a new record, or update one that already exists, the process of saving that data is normally taken care of automatically "behind the scenes."  All we need to do is move to or create a new record, close the form, or close the database, and Access saves the original record for us.

Record Navigation Buttons: when a record is added or changed, moving to a new or different record 

via the navigation buttons, will save the origin form record automatically.

However, there are times when it may necessary to save a record manually.  Suppose, for example, we have two tables joined together in a one to many relationship. We create a new record on a form bound to a table on the one side of the relationship, and then click a button to open a new form bound to a table on the many side of the relationship.  The newly opened form is then used to create a sub-record for the parent record on the original form.

Parent and sub-forms: In this screenshot, the parent form "Work Session" is bound to the tbl_work_session table, and the "Entry Log" form is bound to the tbl_entry_log table.  There is a one to many relationship between the two tables, with tbl_work_session being on the one side of the relationship.

The problem in this scenario is that when we come to close the form for the sub-record (thereby saving the data to the underlying table on the many side of the relationship), the parent record on the original form has not, at this stage, been inserted into the underlying table on the one side of the relationship.  That is to say, Access has not got around to autosaving the parent record since the parent form has not moved to any other record, or been closed etc - all we have done is click a command button to open a new form.  As such we get an error message saying "You cannot add or change a record because a related record is required in [the parent] table"; this is because the rules of referential integrity have been broken (ie the sub-record in the many table requires a parent record in the one table).

However, it is simple to overcome this issue. We just need to save the parent record manually before trying to close the sub-record form.  A quick and easy way to do this is to click the pencil symbol on the top left of the parent-form, or the SAVE icon in the RECORDS group of the HOME ribbon. This will save the parent record, thereby allowing it's sub-record to be saved afterwards.

 The pencil symbol (above left) can be used to save the current record manually.  Alternatively, the SAVE icon in the RECORDS group of the HOME ribbon (above right) can also be used.

However, this is not particularly useful from the perspective of user-friendliness. That is to say, a user may not realise or understand that this action is necessary.  A better way of doing it would be to save the parent record automatically when the user clicks the command button to open the sub record form. As such, we need to alter the open form button (referred to as "CREATE NEW ENTRY" in our example above) so it also becomes a custom save button.  To do this, we just add the following line of VBA code to the buttons ON CLICK event:

Application.RunCommand acCmdSaveRecord

See the screenshot below for the full code:

VBA code to save the parent record before opening the sub-record form.

---

Creating a Custom Save Button from Scratch - A Tutorial

Here is a step by step tutorial for creating a custom save button from scratch on Access for Office 365:

1. Open the form in DESIGN VIEW.
2. Go to the CONTROLS group of the CREATE ribbon.
3. Click the MORE button () to the lower right of the control icons, and de-select the USE CONTROL WIZARDS icon when the additional options appear. 

   

   Above: additonal  options below control icons.

4. Click the BUTTON control icon () from the CONTROLS ribbon. The cursor will now change to a button icon when it enters the design grid.
5. Position the cursor at the desired location on the design grid, and click the left hand mouse button.  This will create the new button at the selected location.
6. Select the new BUTTON contol by left clicking it, and open the PROPERTIES sheet from the TOOLS group of the DESIGN ribbon (if it is not already open).
7. Click the EVENTS tab on the properties sheet, and left click the ON CLICK row of the events tab.
8. Click the ELIPSE button (three horizontal dots) which appears on the right hand side of the row.

   

   Above: the ON CLICK event row on the PROPERTIES sheet.

9. Select CODE BUILDER from the CHOOSE BUILDER dialog form which opens.  This opens the VBA editor.
10. Enter the following code in the CLICK sub procedure for the control button.

Above: The CLICK sub procedure for the new control button shown in the VBA editor.

Now when the user clicks the new button at runtime, any data s/he entered will be saved to the corresponding record in the forms underlying table.