Decisions
Programs, unless they are extremely simple, usually have to make decisions according to data retrieved or input from the user. Decision making is one of the most important areas of programming, because it specifies what will happen when different events occur.
A good example of a common programming decision is IF something is true, THEN do action1, or ELSE do action2. In everyday life, this might be the statement "IF it is raining, THEN carry an umbrella, or ELSE (if the condition is not met and it is not raining) carry sunglasses."
The following is some sample code to show how the conditional If..Then..Else statement works and how it produces different results. Enter this in the module you created (this must be in a module that you have inserted yourself). See below for an example of what your code window should look like.
Sub Test_If() If CurrentDb.Name= "C:\Temp\MyDB.accdb" Then MsgBox "Database path and name are correct" Else MsgBox "Database has been re-named/moved" End If End Sub
This example refers to the current database name in the line CurrentDb.Name. In Access, this will give the full path and name of the Access file. Run this code, replacing "C:\Temp\MyDB .accdb" with the path and name of your own file. You will get the message that the database path and name are correct.
Save the code and then go into Windows Explorer and make a copy of the database file. Load the file in and re-run the code again in the new file and you will get the message box saying that the database has been renamed/moved.
You can see that a simple piece of code like this checks where the database was loaded from and checks that it has not been renamed. This might seem straightforward, but there are times when the integrity of the database relies on this information being correct.
Above example the code to separate the main parts of the conditional statement-this makes it easier to read and easier to debug because you can instantly see the groups of statements and where they start and finish. It is possible for If statements to be nested inside each other, so you can have an If statement within an If statement; this frequently happens in more complicated programs. (See the section "Looping," later in this tutorial, for more on how this works.) It is convenient to be able to see at a glance where one If statement starts and ends. If others are in between your start and stop point, you can frequently get lost when debugging code.
The End..If statement shows where the conditional statements finish, or you can put the entire If statement onto one line, which then would not require an End..If, as shown here:
If CurrentDb.Name= "C:\Temp\MyDB.accdb" Then MsgBox _ "Database path and name are correct"
However, note that a continuation character (space and underscore) has been used to get all the code in, and you cannot use the continuation character between quotes (as part of a string).
If you have multiple instructions to be executed, you can place the statements on a single line as long as you separate each statement with a colon. However, this can become very difficult to read and debug, and often several instructions must be carried out that preclude putting everything on one line.
Conditional operators that can be used are as follows:
| Operator | Meaning |
|---|---|
| = | Both numbers or values are equal. This condition will also work for values, such as "dog" and "cat." |
| < | First value is less than second value. |
| > | First value is greater than second value. |
| <= | First value is less than or equal to second value. |
| >= | First value is greater than or equal to second value. |
| <> | First value is unequal to second value. |
An expression such as x=1 is evaluated as a Boolean value, which is True or False or Non-zero or Zero. This means you do not always have to use an operator. If you are only interested in whether a variable has a non-zero value in it, then you can use
If MyVar Then MsgBox "MyVar has a value"