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:
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.
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.
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.