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

IfCondition Then
   ' statements go here . . .
ElseIf AnotherCondition Then
   ' more statements go here . . .
Else
   ' 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.

rs.Edit

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

rs.Update

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 counter is incremented by 1. When counter exceeds the value end, the block of code is no longer executed. Thus, the code block is executed a total of end - start + 1 times, each time with a different value of counter.

Note that we can omit the word counter in the last line of a For loop (replacing Next counter with just Next). This may cause the For loop to execute a bit more quickly, but it also detracts a bit from readability.

To illustrate, the following code prints the names of the fields in the Objects table:

Sub PrintFields(  )

Dim i As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Objects")

For i = 0 To rs.Fields.Count - 1
   Debug.Print rs.Fields(i).Name
Next

rs.Close

End Sub

Note that the limits of the For statement are 0 to rs.Fields.Count - 1 because the fields are indexed starting at 0 (rather than 1). We will discuss this issue in more detail when we talk about DAO programming.

For loops are often used to initialize an array. For instance, the code:

For i = 0 To 10
    iArray(i) = 0
Next i

assigns a value of 0 to each of the 11 variables iArray (0) through iArray (10).

Note that the loop variable counter will usually appear within the block of code, as it does in this array-initialization example, but this is not a requirement. However, if it does appear, we need to be very careful not to change its value, since that will certainly mess up the For loop. (VBA automatically increments the loop variable each time through the loop, so we should leave it alone.)

VBA provides the Exit For statement to exit a For loop prematurely. For instance, the code in Example 13-1 finds the first field whose type is Integer.

Sub FindFirstIntegerField(  )

Dim i As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Objects")

For i = 0 To rs.Fields.Count - 1
   If rs.Fields(i).Type = dbInteger Then Exit For
Next

If i < rs.Fields.Count Then
   ' First Integer field found
Else
   ' No such field exists
End If

rs.Close 

End Sub

We can also control the step size and direction for the counter in a For loop using the Step keyword. For instance, in the following code, the counter i is incremented by 2 each time the block of code is executed:

For i = 1 to 10 Step 2
   ' code block goes here
Next i

The following loop counts down from 10 to 1 in increments of -1. This can be useful when we want to examine a collection (such as the cells in a row or column) from the bottom up.

For i = 10 to 1 Step -1
   ' code block goes here
Next i

The For Each loop is a variation on the For loop that was designed to iterate through a collection of objects (as well as through elements in an array) and is generally much more efficient than using the traditional For loop. The general syntax is:

For EachObjectVar In CollectionName

   ' block of code goes here . . .

Next ObjectVar

where ObjectVar is a variable of the same object type as the objects within the collection. The code block will execute once for each object in the collection.

The following version of PrintFields uses a For Each loop. It is more elegant than the previous version (and more efficient as well):

Sub PrintFields2(  )

Dim fld As Field
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Objects")

For Each fld In rs.Fields
   Debug.Print fld.Name
Next

rs.Close
 
End Sub

Thus, when iterating through a collection of objects, we have two choices:

For Each object in Collection
   ' code block here
Next object

or:

For i = 1 to Collection.Count
   ' code block here
Next i

It is important to keep in mind that the For Each loop can be much faster than the For loop when dealing with collections of objects.

The Do loop has several variations. To describe these variations, we use the notation:

{While | Until}

to represent either the word While or the word Until, but not both. With this in mind, here are the possible syntaxes for the Do loop:

Do {While | Until} condition

   ' code block here

Loop

or:

Do

   ' code block here

Loop {While | Until} condition

Actually, there is a fifth possibility, because we can dispense with condition completely and write:

Do

   ' code block here

Loop

The Do loop is used quite often in DAO programming to iterate through a recordset. Here is a typical example that prints all values of a particular field in a recordset:

Sub DoExample(  )

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Objects")

rs.MoveFirst

Do While Not rs.EOF
   Debug.Print rs!Name
   rs.MoveNext
Loop

rs.Close

End Sub

We will discuss the EOF property, as well as the MoveFirst and MoveNext methods, when we discuss Recordset objects later in the book.

Just as the For loop has an Exit For statement for terminating the loop, a Do loop has an Exit Do statement for exiting the Do loop.

As we have seen, the If...Then... construct is used to perform different tasks based on different possibilities. An alternative construct that is often more readable is the Select Case statement, whose syntax is:

Select Case testexpression
   Case value1
      ' statements to execute if testexpression = value1
   Case value2
      ' statements to execute if testexpression = value2

   . . .

   Case Else
      ' statements to execute otherwise
End Select

Note that the Case Else part is optional. To illustrate, the following code is the Select Case version of Example 12-1 in Chapter 12 (see the discussion of the Section 12.4.3) that displays the type of a file based on its extension. I think you will agree that this is a bit more readable than the previous version:

Sub ShowFileType(FileExt As String)

Dim FileType As Variant

Select Case FileExt
  Case "mdb"
    FileType = "Database"
  Case "txt"
    FileType = "text"
  Case "dbf"
    FileType = "dBase"
  Case Else
    FileType = "unknown"
End Select

' Display result
MsgBox FileType

End Sub

Note that VBA allows us to place more than one condition in the same Case statement (separated by commas). This is useful when more than one case produces the same result.

There is a lot more to the VBA language than we have covered here. In fact, the Microsoft VBA reference manual is about 300 pages long. However, we have covered the main points needed to begin Access VBA/DAO programming. (For a reference on the VBA language, you might want to check out the book VB & VBA in a Nutshell, by Paul Lomax, also published by O’Reilly.)

Actually, many Access VBA programming tasks require only a small portion of VBA’s features, and you will probably find yourself wrestling much more with DAO’s object model than with the VBA language itself.

I conclude our discussion of the VBA language per se with a brief outline of topics for further study, which you can do using the VBA help files .

Open pathname For mode As [#]filenumber