Chapter 13. Control Statements

I conclude our discussion of the VBA language with the main VBA control statements, which are statements that affect the flow of control (or flow of execution) in a program.

The If ...Then Statement

The If...Then statement is used for conditional control. The syntax is:

IfCondition Then
   ' statements go here . . .
ElseIf AnotherCondition Then
   ' more statements go here . . .
   ' more statements go here . . .
End If

Note that we may include more than one ElseIf part, and that both the ElseIf part(s) and the Else part are optional. We can also squeeze all parts of this statement onto a single line, which is generally only a good idea when the ElseIf and Else parts are not required.

To illustrate, the following code checks to see if the FirstName field is null. If so, it replaces the Null value with a question mark. If not, it capitalizes the first name.


If IsNull(rs!FirstName) Then
   rs!FirstName = "?"
   rs!FirstName = UCase(rs!FirstName)
End If


The For Loop

The For...Next statement provides a method for repeatedly looping through a block of code (that is, one or more lines of code). This loop is naturally referred to as a For loop. The basic syntax is:

For counter = start To end

   ' block of code goes here . . .

Next counter

The first time that the block of code is executed, the variable counter (called the loop variable for the For loop) is given the value start. Each subsequent time that the block of code is executed, the loop variable ...

Get Access Database Design & Programming, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.