Learning Access VBA - A Beginners Guide

This post is all about getting started with the Access VBA programming language -  a first tutorial starting right at the beginning.  As you may already know, the Access Database comes with its own programming language called Visual Basic for Applications, or VBA.  It offers a powerful way to automate and generally control how an access database application operates.  However, whilst relatively easy to use, getting started can seem a little daunting to the newcomer.  As such, this tutorial is intended to help the beginner feel at home in the programming environment.

Figure 1: The Visual Basic Icon

The first problem faced by the beginner is finding out how to get into this thing called VBA.  This is because, technically, VBA is a separate software package which works alongside Access.  That said, we can only open the VBA Editor (see figure 2 below) from within the Access database itself.  The most direct way of doing this is by clicking the VISUAL BASIC icon (located in the MACRO's group of the DATABASE TOOLS ribbon - see figure one above).  You might like to download the Learning Visual Basic Sample Database, and give this a try.

Learning Visual Basic Sample Database

Figure 2: The VBA Editor

The sample database consists of an unbound form which acts as a simple calculator.  There are three Text Boxes and two Command Buttons.  The user enters two numbers in the top two text boxes and clicks the  equals (=) buton.  This runs a block of VBA code (called a sub procedure) which reads the numbers from the two text boxes, adds them together, and displays the result in the third text box.  The user can then click the Clear button (C). This runs a different block of code to clear all the existing numbers from the text boxes so new numbers can be entered and calculated.

Figure 4: The Simple Calculator form
described in the paragraph above.

So lets take a look at how this is created.  We shall do so in two stages: first we shall create the form, then open the VBA Editor and enter the two blocks of code.

Creating the Form

1. Click the FORM DESIGN icon (located in the FORMS group of the CREATE ribbon).
2. Drag and drop three text boxes on the the form (if you need help, you might like to check out my tutorial on how to Create an Access Form from Scratch).
3. Next drag and drop the two Command Buttons onto the design grid.  Make sure the USE COMMAND WIZARDS icon is not highlighted when you do this - otherwise just click the CANCEL button if the Command Button Wizard opens.
4. Click on the label for the top text box and change the text to read "First Number".
5. Repeat step 4 for the second and third labels, entering "Second Number" and "Result" respectively.
6. Then click on the top text button and change the text to "C". This will be the Clear Button.
7. Do the same for the lower text button, changing the text to "=".  This will be the Equals Button.
8. Next click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).
9. Set the NAME property (located at the top of the OTHER TAB of the PROPERTY SHEET) for the first text box to txtFirstNumber.  
10. Repeat step 9, calling the lower two text boxes txtSecondNumber and txtResult, and calling the Command Buttons ctlClear and ctlAdd. 

NB It is important to enter the NAME properties correctly.  Although we could have called them anything we wanted, our VBA code refers to these form controls via the NAME property.  As such we need to ensure that the names used on our form are going to match those to be used in the code.  This is an important principle to grasp as we use VBA alongside Access.

Entering the VBA Code

1. Next click on the ctlAdd (=) Command Button whilst our form is still open in DESIGN VIEW.
2. Open the PROPERTIES SHEET (if it is not already open) and click the EVENTS tab ctlAdd.
3. Click the cell for the ON CLICK property.  Then click the three dots button (...) at the right of the cell.  This opens the CHOOSE BUILDER dialog box.
4. Select CODE BUILDER from the menu items, and click OK.  This opens the VBA Editor in the exact location where we are going to enter the code which is triggered when the user clicks the button at runtime (the ON CLICK Event).  It even writes the first and last line of code which makes this a self contained block of code (a Sub Procedure).
5. Enter the code (listed below) between the Private Sub and End Sub lines:

Private Sub ctlAdd_Click()

    Dim varFirstNumber As Double

    Dim varSecondNumber As Double

    Dim varResult As Double

    

    If IsNull(Me!txtFirstNumber) = True Or IsNull(Me!txtSecondNumber) = True Then

        MsgBox "Please Enter Numbers in Both Textboxes", vbInformation, "Missing Number(s)"

        Exit Sub

    End If

    

    varFirstNumber = Me!txtFirstNumber

    varSecondNumber = Me!txtSecondNumber

    

    varResult = varFirstNumber + varSecondNumber

    

    Me!txtResult = varResult

        

End Sub

Now repeat all of the last stage from 1 to 5, but this time for the ctlClear Command Button.  Enter the following code in the same manner as above:

Private Sub ctlClear_Click()

    Me!txtFirstNumber = Null

    Me!txtSecondNumber = Null

    Me!txtResult = Null

    

End Sub

When this is complete, you may save your code by clicking the SAVE icon, and close the VBA Editor.  To test the code simply open the form in FORM VIEW, enter the numbers to calculate, and click the ctlAdd button.  The correct result should display in the third text box once this is done.

How the Code Works


Please don't worry about being able to create this code yourself at this stage in the learning process.  The main purpose of this tutorial was to get you familiar with the programming environment, rather than how to construct the code.  I intend to do this in future tutorials.  However, we will go through each line of the first sub procedure just to give you a general idea of how the programming code works.

The code is triggered by the Command Button's ON CLICK event.  The user clicks the command button, Access 'raises' the ON CLICK event, and the block of code runs one line at a time.

Private Sub ctlAdd_Click()
The first line, the SUB Statement, was created automatically by Access when we opened the VBA Editor from the property sheet.  It's purpose is to mark the beginning of this block of code or Sub Procedure.  The PRIVATE part of the statement relates to the concept of Scope.  We won't worry too much about what this means at this stage, except to say it is concerned with where the sub can be 'called' from.  Private means just that - it is private to this particular form module, and can not be called from a module elsewhere in the application.  The part which says ctlAdd_Click is the name of the sub procedure. 

Dim varFirstNumber As Double

Dim varSecondNumber As Double

Dim varResult As Double
The next three lines of code declare three separate variables.  The purpose of variables are to enable values to be temporarily stored, processed, and retrieved.  More about this soon. At this stage we are just going to note that we have used the DIM Statement to define three variables called varFirstNumber, varSecondNumber and varResult.  We have also included AS DOUBLE to tell Access that the variables are going to store values of the DOUBLE data type (so we can calculate decimal numbers). 

 If IsNull(Me!txtFirstNumber) = True Or IsNull(Me!txtSecondNumber) = True Then

     MsgBox "Please Enter Numbers in Both Textboxes", vbInformation, "Missing Number(s)"

     Exit Sub

 End If
This next section of code is an IF Statement, and  forms a self contained block of code within the sub procedure itself.  It was something I added to ensure the user enters a number in each of the two text boxes. If it detects that there is a Null value it runs the nested code to display a message box, and then exits the sub. 

varFirstNumber = Me!txtFirstNumber

varSecondNumber = Me!txtSecondNumber

In this section of code, we are assigning each of the two variables with a value.  The equals sign assigns the variable name on the left of the sign with the value on the right.  In this case the top line is setting the value of the varFirstNumber variable to the value entered by the user in the txtFirstNumber text box control.  Note that the text box is referenced by means of the code: Me!txtFirstNumber.  The Me! part of this is telling access that the text box belongs to the form to which this VBA module is attached ie frmCalculator; and txtFirstNumber is the name we gave to the top text box on the form we created earlier.

varResult = varFirstNumber + varSecondNumber
This line is assigning the variable varResult with a value.  In this case the value being assigned is the sum of the values stored in varFirstNumber and varSecondNumber.  Put another way, this line is performing the addition calculation and storing the result in the variable varResult.

Me!txtResult = varResult
This line is using the value stored in the varResult variable to set the value to be displayed in the lower text box of the form, txtResult.  It references the text box in the manner already described above.  This time, however, the text box reference is on the right hand side of the equals sign, and the variable on the left.  This is because the value of the variable is being retrieved, and the value of the text box is being set.


End Sub
This line was generated automatically just like the first in this block of code.  However, this time it is marking the end of the sub procedure.


Try this Yourself
If you are feeling adventurous, you might like to try creating more Command Buttons for the Calculator form.  Try creating a button to multiply or subtract the values stored in the top two text boxes.  You can follow the same procedure as before.  However, when you copy and paste the code, change the addition operator (+) in the appropriate line for multiply (*) and subtract (-) respectively.