Access VBA For Loop: Syntax, Examples, and Common Errors

VBA & macros · Updated November 11, 2026

In Access VBA, use a For...Next loop to repeat instructions a set number of times, and use For Each...Next to process every item in a collection, such as a form’s controls. Choose the loop that matches the task: a counter-based loop is suited to numbered repetitions, while a recordset loop is usually better for moving through database records.

Basic For…Next syntax

A For...Next loop starts with a counter, assigns it a starting value, and repeats the statements inside the loop until the counter passes its limit. In a VBA procedure, its general structure is:

For counter = startValue To endValue [Step increment]
    ' Statements to repeat
Next counter

The Step part is optional. If you leave it out, VBA increases the counter by 1 each time. The starting and ending values are included when the loop runs, provided the step moves the counter toward the ending value. This example prints five numbers in the Visual Basic Editor’s Immediate window:

Public Sub PrintFiveNumbers()
    Dim i As Long

    ' Print each number from 1 through 5.
    For i = 1 To 5
        Debug.Print i
    Next i
End Sub

In this example, the loop runs with i equal to 1, 2, 3, 4, and 5, so it prints five lines. The word after Next is optional, but including the counter name makes longer procedures easier to read and helps you match each loop’s beginning and end.

How the counter and Step value work

Use Step when the counter needs to change by an amount other than one. For example, For i = 1 To 9 Step 2 visits 1, 3, 5, 7, and 9. A descending loop needs a negative step, as in For i = 5 To 1 Step -1; that loop visits 5, 4, 3, 2, and 1. If the step points away from the ending value, the loop body does not run.

VBA advances the counter automatically after each pass. Avoid changing that same counter inside the loop unless you have a specific reason: doing so can make the sequence harder to predict. Use a separate variable for other calculations. Also choose a counter type with enough range. Long is a common choice in Access VBA; Integer has a much smaller range and can overflow in loops that count past 32,767.

A loop can also contain a decision, so some iterations perform an action and others do not. In this example, only even numbers are printed:

Public Sub PrintEvenNumbers()
    Dim i As Long

    ' Check each number and print only even values.
    For i = 1 To 10
        If i Mod 2 = 0 Then
            Debug.Print i
        End If
    Next i
End Sub

Using For Each with Access collections

Use For Each...Next when you want to visit every object in a collection without managing a numeric index. Access collections include the controls on a form, the fields in a recordset, and the forms in the Forms collection. The loop variable must be an object variable or a Variant, rather than a numeric counter.

This example belongs in a form’s class module. It prints the name of every text box on that form to the Immediate window. The form module matters because Me means the form instance whose code is running:

Private Sub ListTextBoxNames()
    Dim ctl As Access.Control

    ' Visit each control on this form.
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            Debug.Print ctl.Name
        End If
    Next ctl
End Sub

The collection can contain different control types, which is why the example checks ControlType before treating an item as a text box. You can adapt the condition to check for a button, combo box, or another control type. If you put similar code in a standard module, there is no form-specific Me; refer to an open form explicitly, for example through Forms("FormName").Controls, and ensure that form is open.

Looping through Access records

A table or query’s rows are not normally accessed with a numeric For...Next counter. Use a DAO recordset and move its cursor with MoveNext, commonly inside a Do Until...Loop. This pattern visits the records returned by a query, regardless of how many there are:

Public Sub PrintCustomerCities()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    ' Open a read-only snapshot of the selected fields.
    Set db = CurrentDb
    Set rs = db.OpenRecordset( _
        "SELECT CustomerID, City FROM Customers", dbOpenSnapshot)

    Do Until rs.EOF
        Debug.Print rs!CustomerID, rs!City
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Replace Customers and the field names with names from your database. The query can also select records with a WHERE clause or sort them with ORDER BY. The key detail is rs.MoveNext: without it, the cursor remains on the same record and the loop can run indefinitely. Testing rs.EOF before reading fields also handles an empty result set cleanly. A snapshot is appropriate when the example only reads data; use an updatable recordset type when the task genuinely needs to edit records.

Choosing the right loop for the task

Pick the loop based on what is being repeated, not simply on which syntax looks shorter:

  • Use For...Next for a known range, such as processing items 1 through 20 or repeating an operation a fixed number of times.
  • Use For Each...Next to visit every member of a collection, such as each control on a form.
  • Use a recordset with Do Until rs.EOF to process rows returned by a table or query.
  • Use Do While or Do Until when the number of repetitions depends on a condition that is checked as the loop runs.

A common misconception is that a For Each loop automatically means “for each database record.” It means “for each item in a collection.” A DAO recordset does have a Fields collection, so you could use For Each to inspect the fields of the current record, but that does not move through all the records. You still need a recordset cursor loop to visit each row.

Common errors and ways to prevent them

Most loop problems come from a mismatch between the loop’s bounds, its counter, and the data being processed. Check these points when results are missing, repeated, or incorrect:

  • Wrong direction: A descending range needs a negative Step. A positive step from 5 down to 1 does not count down.
  • Off-by-one bounds: The end value is included when the loop reaches it. If an array or collection uses a different index range, check its actual lower and upper bounds rather than assuming they start at 1.
  • Wrong counter type: Use a Long for ordinary numeric counting when the total could exceed the Integer range.
  • Wrong variable for For Each: The loop variable must be an object variable or Variant. A variable declared as Long cannot hold a control object.
  • Recordset never advances: Put MoveNext on the path taken for every record, or a recordset loop may repeatedly process the same row.
  • Unexpected exit: Exit For stops the innermost active For loop. In nested loops, it does not automatically exit every outer loop.

For nested loops, give each loop its own counter and use matching names after Next. For example, an outer loop over departments and an inner loop over months should use separate variables. This makes it easier to see which counter changes at each level and reduces accidental reuse.

Running and checking an Access VBA loop

Access desktop is a Windows application; there is no equivalent Access desktop VBA environment for Mac. In Access for Windows, open the Visual Basic Editor with Alt+F11. To display the Immediate window, use Ctrl+G. Run a procedure from the editor with the cursor inside it by pressing F5, or call it from a form event or another procedure. For example, a button’s Click event can call a public procedure by name.

Use Debug.Print to inspect values without changing table data. You can also set a breakpoint by clicking beside a code line in the editor, then run the procedure and step through it to see how the counter or recordset changes. When a loop produces an unexpected result, verify the start, end, and step values first, then check the condition and the code that advances the data source.

Before running a macro that changes records, controls, or other database content, save a separate copy of the database. Access VBA changes cannot be undone with Ctrl+Z. Start with a small test set or a copy of the data, and use a transaction or other deliberate error-handling strategy for changes that need reliable rollback.

Frequently Asked Questions

How do I write a simple For loop in Access VBA?

Declare a counter, such as Dim i As Long, then write For i = 1 To 5, put the repeated statements on the next lines, and close with Next i. The loop runs once for each counter value from 1 through 5.

What does Step do in a VBA For loop?

Step sets how much the counter changes after each pass. For example, Step 2 skips alternate numbers, while Step -1 counts downward. If omitted, the step is 1.

Should I use For or For Each to loop through records?

Neither is usually the right outer loop for database rows. Open a DAO recordset and use a Do Until rs.EOF loop with rs.MoveNext. Use For Each for a collection, such as the fields in the current record.

Why does my Access VBA loop run forever?

Check whether a condition can become true and whether the value it depends on is changing. In a recordset loop, confirm that MoveNext runs. In a counter loop, check for code that changes the counter unexpectedly or prevents it from reaching the ending condition.

Can I use Exit For in a nested loop?

Yes. It exits the innermost active For or For Each loop, then execution continues after that loop. To leave multiple nested loops, use a clear flag or restructure the procedure rather than assuming one Exit For exits them all.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.