Access Database Design and Programming, Second Edition

Exit For

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.

Example 13-1. Finding the First Integer Field

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