Access Database Design and Programming, Second Edition

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 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 to rs.Fields.Count - 1 because the fields are indexed starting at (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) ...