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:
- Create a new unbound Access Form.
- Add two text boxes called txtFirstNumber and txtSecondNumber.
- Add a Command Button called ctlCompare.
- Select the Command Button and open the PROPERTIES SHEET.
- Click the Events tab of the PROPERTY SHEET.
- Click the three dots symbol (...) on the right of the ON CLICK property cell.
- Open the VBA Editor by clicking CODE BUILDER from the CHOOSE BUILDER menu.
- Copy and paste the code listed below (excluding the first and last line which should have been added to the VBA editor automatically).
- 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.
No comments:
Post a Comment