Using Events and Manipulating Property Settings - Learning Access VBA - Tutorial 4

This is the last post in the series of introductory tutorials on Learning Access VBA.  So far we have covered a wide range of areas - the VBA Development Environment, variables, referencing form controls, the conditional IF ... THEN... ELSE statement, and Loops.  There is still much more we could have covered, even at an introductory level (arrays for example).  However, I wanted to end the series by focusing on how we use VBA to automate the access database applications that we create.  To do this we are going to examine how to trigger our VBA code through tapping into events and manipulating form property values.

Using Events

If you have been following the Learning Access VBA series, you will already be familiar with the Command Button's ON CLICK event.  The user clicks the command button at run time thereby firing the ON CLICK event, which in turn triggers any code that we have written in that event's sub routine. There are many more events which we can also use to trigger our code.  A text box control has, for example, an ENTER event and an EXIT event.  The ENTER event fires when the user moves the cursor into the text box, and the EXIT event fires when it moves out again.  The code that you may attach to such events depends entirely on the particular needs of the application.  The point is, they are there to use if we have written any code to trigger in response to the particular event in question.

So far I have only mentioned events for individual controls.  Some of the more important events, however, occur at Form or Report level.   These include the Forms ON CURRENT, BEFORE UPDATE and ON LOAD events.  The ON CURRENT event fires just before a form displays a new record; BEFORE UPDATE occurs just before the record is saved; and the ON LOAD event occurs as a form loads data contained in a table, or derived from a query.

You can see the whole range of form or reports events by opening the PROPERTIES pane (whilst in design view) and clicking the EVENTS tab.  In today's exercise, however, we are just going to focus on the form ON OPEN event.  This fires as the form opens, but before any data is loaded from the forms RECORD SOURCE.  As such it occurs before the ON LOAD even which was mentioned above.  Consequently, this is a good event to use for triggering code for tasks like prompting the user for parameters, applying filters, or even changing the form's RECORD SOURCE property.  In the exercise we shall be doing, we are going to use the ON LOAD event to determine whether a form opens in DATA ENTRY, EDIT or read only mode. Before we begin, lets first take a look at how we can manipulate form properties using VBA code.

Figure 1: Some of the Events listed in the
EVENTS tab of the PROPERTY SHEET.

Manipulating Properties

You may already have experience of setting properties in Design View using the PROPERTIES sheet .  With VBA we can also read, test and dynamically change property setting during runtime.  The key to doing this is understanding how to correctly reference the property of the control, form or report.  This task is easier when our code is contained within the VBA Module of the form or report being referenced.  Suppose we want to reference the DATA ENTRY property of a form called frmCustomers.  If we were referencing the property from the VBA module attached to frmCustomers we can use the ME keyword.  This acts as a short cut when referencing the attached form.  This is how it works:

Me.DataEntry


If, on the other hand, we were attempting to make the same reference from a module outside of frmCustomer we would need to write the full reference like this:


Forms![frmCustomers].DataEntry


As you can see, we not only need to write the name of the form in question but we also need to specify the FORMS collection object to which it belongs.  It is also worth pointing out that in VBA we use the exclamation mark (!) to separate two objects when the preceding object belongs to the one coming after, but we use the full stop (.) to separate an object from one of its properties.  Any form, report or control name in the reference also need to be contained within square brackets if the name contains a space or a reserved word (but this is not essential if the name is constructed without these 'problematic' elements).


Now we know how to create property references, we can use them in our code to read, test or dynamically change property values. For example:


To store a property setting in a variable called varDataEntry:
    varDataEntry = Me.DataEntry


To test the value of a property setting:
    If Me.DataEntry = true Then


To change the value of a property setting:
    Me.DataEntry = False


Exercise

In the following exercise we are going to create a switchboard form with three command buttons - these are ctlAdd, ctlEdit and ctlRead. All three buttons open the same form, frmContacts, but the first button opens the form in Data Entry mode, the second in Edit mode and the third as Read Only.  So how does it do this?

Figure 2: frmSwitchboard

When the user clicks one of the command buttons, it fires the command button's ON CLICK event where there is some VBA code to open the form with the DoCmd.OpenForm method.  One of the parameters of the OpenForm method passes an OpenArgs parameter to frmContact.  The value of this parameter (which is a string) is then stored by Access in frmContacts OPENARGS (Open Argument's) property.

As frmContacts opens, the ON OPEN event fires, triggering another block of VBA code.  This code tests the value of the forms OPENARGS property using the IF ... THEN ... ELSE statement. This is to ascertain whether the value contained in the OPENARGS property is  "Add", "Edit" or "Read".  These were passed by the OpenForm method as mentioned above.  If the OPENARGS property value is "Add" our code will set the form's DATA ENTRY property to TRUE; if the value is "Edit", DATA ENTRY is set to FALSE; if "Read", the DATA ENTRY, ALLOW ADDITIONS, ALLOW DELETIONS, and ALLOW EDITS properties are all set to FALSE.

You can download the completed solution by clicking this link: Events and Properties Exercise.  The instruction for creating the exercise follow below:

Stage One - Creating the Switchboard

1. Create an unbound switchboard form called frmSwitchBoard.
2. Add three Command Buttons called ctlAdd, ctlEdit, and ctlRead.
3. Attach the following code to the Command Button's ON CLICK event - see the first tutorial in the Learning AccessVBA series if you need help doing this.

Private Sub ctlAdd_Click()
    DoCmd.OpenForm "frmContacts", , , , , , "add"
End Sub


Private Sub ctlEdit_Click()
    DoCmd.OpenForm "frmContacts", , , , , , "edit"
End Sub


Private Sub ctlExit_Click()
    Application.Quit   
End Sub


Private Sub ctlRead_Click()
    DoCmd.OpenForm "frmContacts", , , , , , "read"
End Sub

NB You may have noticed there are a number of blank parameters passed in our use of the OpenForm method - hence the comma's .  That is because many of the parameters are optional.  We are just passing two parameters - the name of the form to be opened, and the OpenArgs parameter as discussed above.   Incidentally we could have used the DataMode parameter to accomplish the object of this exercise more directly, but I wanted  to demonstrate the working of the form ON LOAD event.

Stage Two - Creating frmContacts


Before you create frmContacts you will first need to create the table - tblContacts - which is going to be its record source.  It's not particularly import which fields are used, but the example database I created has four fields - ID, FirstName, Surname and Company, and the following sample data:

Figure 3: tblContacts with sample data.

Figure 4: frmContacts

1. Create a new form called frmContacts
2. Set the forms RECORD SOURCE property to tblContacts.
3. Add the four fields of tblContacts to the form
4. Add the following code to the forms ON OPEN event:

Private Sub Form_Open(Cancel As Integer)

    If Me.OpenArgs = "add" Then

        Me.DataEntry = True

    ElseIf Me.OpenArgs = "edit" Then

        Me.DataEntry = False

    ElseIf Me.OpenArgs = "read" Then

        Me.DataEntry = False

        Me.AllowAdditions = False

        Me.AllowDeletions = False

        Me.AllowEdits = False

    End If 

  

End Sub

When you have done this, save your work, and open the switchboard to try out the different buttons.

This concludes the Learning Access VBA series of tutorials.  I hope you have found them helpful, and that they have given you a basic introduction to Access programming .  I intend to cover more advanced VBA topics next year.