Evaluating Conditions with the If...Then...Else Statement - Learning Access VBA. Tutorial 2.

This the second post in the Learning Access VBA series.  Last week I introduced you to the VBA Development Environment where we we also learnt a little about variables.  We completed a simple exercise which referenced the values entered by the user into two text boxes, stored the values in variables, and added the values contained therein together. The result was then displayed in a third text box.  This week we are going to move on, and look at how VBA deals with Conditions using the If ... Then ... Else Statement.

So what do we mean by the term conditions or the Conditional?  Basically as our VBA code is executed (that is to say, as the code runs, line by line, processing each statement in turn) the program flow may well reach various points where it needs to branch according to whether a given condition has or has not been met.   For example, we may want our code to run a short block of nested code if the value of a specified variable is greater than a given number.  If the condition is met, the program flow runs the statement contained in the nested code below; whereas, if the condition is not met, the program flow may branch to the next statement after the nested code - the nested code is, in effect, skipped.

Lets take a look at how the If ... Then ... Else Statement is constructed:

IF conditon is met THEN

        nested code1

ELSE

        nested code2

END IF
 
The IF keyword tests whether the condition is true or not.  If it is true, the THEN keyword directs the program flow to the code contained in nested code1.  ELSE is optional. If we use it, ELSE directs the program flow to that contained in nested code2 if the condition after the IF keyword had not been met.  Had we not used the ELSE keyword (with nested code2), the program flow would have skipped nested code1 and gone straight to the END IF keyword.

So, for example, our code may read: 

IF varFirstNumber > varSecondNumber THEN

        MsgBox("First Number is Greater than Second Number")

ELSE

        MsgBox("First Number is not Greater than Second Number")

END IF

This tests whether the number contained in variable varFirstNumber is greater than that contained in varSecondNumber.  If the condition was met, the nested code below the IF keyword would run.  This displays a message box saying "First Number is Greater than Second Number".  If the condition was not met, the nested code below the ELSE statement would have run instead.  This would have displayed a message box saying "First Number is Not Greater than Second Number".  The END IF keyword closes the If ... Then ... Else Statement.


There is one other optional keyword which is well worth mentioning - that is ELSEIF.  When we only use IF/THEN and ELSE, we have just two possible branches in the program code.  However, suppose there are three or more possible conditions?  For example, suppose we want to test whether the variable varFirstNumber is (a) greater than, (b) less than, or (c) equal to, the second variable?  ELSEIF is a good way to do this.  Lets see how we would code this scenario:

IF varFirstNumber > varSecondNumber THEN

        msgbox("First Number is Greater than Second Number")

ELSEIF varFirstNumber < varSecondNumber THEN

        msgbox("First Number is Less than Second Number")
ELSE
        msgbox("First Number is Equal to Second Number")

END IF

As we can see, the ELSEIF keyword has allowed us to enter a second specified condition to test, in the event of the initial condition proving false.  


As such, when the program flow reaches the If ... Then ... Else Statement, the initial condition is tested by the first IF keyword; if this is true, the nested code in the line directly below is executed, and then the program flow branches to the END IF keyword at the bottom of the statement.  However, if the first condition had been false, the first block of nested code is skipped, and the program flow goes to the ELSEIF keyword and the second conditional expression is then tested; if this proves true, the second block of nested code is executed.  However, if the 2nd expression had also proven false, then the 2nd nested code would also be skipped, and the program flow would move down to the ELSE keyword.  There is no condition to test here, so the 3rd block of nested code is executed unconditionally.


(NB In the above scenario there were three possible outcomes.  However, it is also worth mentioning that by adding additional ELSEIF keywords into the statement, the number of possible conditions to test is potentially endless.)


Compare Numbers Exercise


Let's end this tutorial by applying what we have learnt above to the following exercise.    

We are going to create a form with two text boxes named txtFirstNumber and txtSecondNumber.  Then we will add a command button, ctlCompare, with some code attached to it's ON CLICK event.  We learnt how to do this in last weeks Learning Access VBA tutorial. However this time our code will compare the numbers entered into the two text boxes by the user at runtime, and display a message box informing the him/her whether the First Number is (a) Greater than, (b) Less than, or (c) Equal to, the Second.

This is a screenshot of our form in action:

Here the user has entered 10 in txtFirstNumber and 20 in txtSecondNumber.
After the user clicks the Compare command button, Access runs the VBA code
that compares the two numbers and displays the appropriate message.

Instructions

I won't go into great detail regarding instructions - we should have leant all the required skills from last weeks post on Learning Access VBA.  There is also more information about creating an unbound Access Form in the post about Creating an Access Form from Scratch.  I advise you to check out these posts if you need a reminder:

1. Create a new unbound Access Form.
2. Add two text boxes called txtFirstNumber and txtSecondNumber.
3. Add a Command Button called ctlCompare.
4. Select the Command Button and open the PROPERTIES SHEET.
5. Click the Events tab of the PROPERTY SHEET.
6. Click the three dots symbol  (...) on the right of the ON CLICK property cell.
7. Open the VBA Editor by clicking CODE BUILDER from the CHOOSE BUILDER menu.
8. Copy and paste the code listed below (excluding the first and last line which should have been added to the VBA editor automatically).
9. Click the save Icon and close the VBA Editor.

Private Sub ctlQuestion_Click()

    Dim varFirstNumber As Integer

    Dim varSecondNumber As Integer

    

    varFirstNumber = Me!txtFirstNumber

    varSecondNumber = Me!txtSecondNumber

    

    If varFirstNumber > varSecondNumber Then

        MsgBox "First Number is Greater than Second Number"

    ElseIf varFirstNumber < varSecondNumber Then

        MsgBox "First Number is Less than Second Number"

    Else

        MsgBox "First Number is Equal to Second Number"

    End If

    

End Sub

To test out the code, open your form in FORM VIEW, enter a number in each of the text boxes, and click the COMPARE button.  NB for clarity there is no code to validate whether two numbers have been entered in the text boxes.  An error message will result if one or both of the text boxes is empty when the compare button is clicked.

The code begins by declaring two variables, and then assigns values to each one by referencing the two respective text boxes (see Learning Access VBA for more information about this process).  The If ... Then ... Else statement then tests whether the first number is greater than, less than or equal to, the second number.  A message box opens containing the result.