Access VBA Macro Programming

Decisions

Programs, unless they are extremely simple, usually have to makedecisionsaccording 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 doaction2. 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 in Chapter 2

(this must be in a module that you have inserted yourself). See Figure 4-1 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 lineCurrentDb.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.

Notice in Figure 4-1 that I have indented 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 chapter, 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.

36 Microsoft Access 2010 VBA Macro Programming