MS Access VBA For Loop: Syntax, Examples, and Tips

VBA & macros · Updated November 17, 2026

Yes. In Microsoft Access VBA, a For...Next loop repeats a block of code for a counter running through a specified range; use For Each...Next to process every item in a collection. The basic form is For counter = start To end [Step increment], followed by the statements to repeat and Next counter.

What a For loop does in Access VBA

A For...Next loop is useful when the number of repetitions is known or can be expressed as a start value, an end value, and an increment. For example, it can count from 1 to 10, visit every third number in a range, or repeat an operation for each position in an array. Access uses the same VBA loop syntax found in other Office applications; what changes is the Access object being worked with, such as a form, report, table, or DAO recordset.

The counter takes successive values as the loop runs. VBA checks the range and increment to decide whether another pass is due, then continues after the statement following Next. The loop does not automatically mean “process every record in a table”: database records are usually handled with a recordset loop or, when possible, a single SQL action.

Basic syntax and a first example

Use a declared numeric variable as the counter. Long is a good default for counters because its range is much larger than Integer. In the Access Visual Basic Editor, press Ctrl+G on Windows to open the Immediate window, then run this procedure and inspect the printed values.

Public Sub CountByThrees()
    Dim number As Long

    ' Print values from 1 through 10, stepping by 3.
    For number = 1 To 10 Step 3
        Debug.Print number
    Next number
End Sub

The output is 1, 4, 7, and 10. The Step value is optional: if omitted, it defaults to 1. Writing the counter after Next is optional in VBA, but including it makes the code easier to read, especially when loops are nested.

Before running a macro that changes data, save a backup copy of the Access database. VBA actions that update records cannot be reversed with Ctrl+Z in the way that ordinary text edits can.

Choosing the start, end, and Step

Use a positive step to count upward and a negative step to count downward. The end value is an inclusive limit when the counter reaches it exactly. If the next increment would pass the limit, the loop ends. A negative step is essential for a descending loop; a positive step with a start value already above the end value does not count backward.

Public Sub PrintCountdown()
    Dim number As Long

    ' Count down from 5 to 1.
    For number = 5 To 1 Step -1
        Debug.Print number
    Next number
End Sub

This prints 5, 4, 3, 2, and 1. A step of 2 in an upward loop would skip alternate values. A step of 0 does not move the counter, so the loop may never finish unless code explicitly exits it. Choose a meaningful, nonzero increment and make sure its direction matches the range.

VBA evaluates the start, end, and step expressions when it enters the loop. If code changes a variable used to calculate the end value while the loop is running, that change does not reset the loop’s original limit. For clarity, avoid changing the counter inside the loop body; let For...Next manage it.

Loop through an array or a form collection

A numeric loop works well when items have positions, but a collection may be easier to process with For Each...Next. The following example works in a form’s class module and prints the name of each control on that form. It does not belong in a standard module because Me refers to the current form or report module.

Private Sub ListFormControls()
    Dim item As Control

    ' Visit every control on this form.
    For Each item In Me.Controls
        Debug.Print item.Name
    Next item
End Sub

For an array, a numeric loop is useful when the index matters. Use LBound and UBound rather than assuming the first element is always zero or one: the declared lower bound can vary. A For Each loop can also iterate array elements when you do not need their index. Be aware that changing an array or collection while iterating it can make the traversal unreliable; build or modify the collection outside the loop when practical.

Why database records usually need a recordset loop

A table or query result is not normally treated like a numbered VBA array. Its row count can change, and records are accessed through a DAO or ADO recordset. A common DAO pattern is to test whether the recordset is at its end, process the current row, and call MoveNext. This example assumes a table named Customers with a CompanyName field, and that the DAO library is available in the project.

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

    ' Open a read-only snapshot of the selected field.
    Set db = CurrentDb
    Set rs = db.OpenRecordset( _
        "SELECT [CompanyName] FROM [Customers] ORDER BY [CompanyName];", _
        dbOpenSnapshot)

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

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

The Do While loop fits this task because it continues until the recordset reports end-of-file. A snapshot is suitable for reading results; use an editable recordset type when you need to update records through the recordset, and handle errors so it is still closed if a problem occurs. If Access does not recognize the DAO.Database or DAO.Recordset types, check the project’s references in the Visual Basic Editor under Tools > References.

When a loop is the wrong tool

Having a loop available does not mean every repeated database task should use one. If the same update can be expressed as a query, a single action query is often clearer and lets the database engine handle the matching rows. For example, setting a status for all overdue orders is usually better expressed as an UPDATE query with a suitable WHERE condition than by opening a recordset and changing each row in VBA.

Loops remain appropriate when each item needs different logic, when the operation involves a form’s controls, or when a task depends on a result calculated from earlier iterations. Before choosing a loop, ask:

  • Is there a fixed sequence or collection to visit? Consider For...Next or For Each...Next.
  • Does the work continue until a condition changes, such as a recordset reaching EOF? A Do loop may express that condition more naturally.
  • Can one SQL statement perform the same action on a set of rows? Prefer the query when it accurately expresses the requirement.

For bulk data changes, test the query on a copy of the database or first run a corresponding SELECT to confirm which records match. A loop is not automatically safer than a query: either approach can change many records if its criteria are wrong.

Common errors and ways to troubleshoot

Small mistakes in loop boundaries can cause missing work, extra work, or a procedure that seems stuck. Use Option Explicit at the top of modules so VBA requires variables to be declared; this helps catch a misspelled counter that would otherwise become a separate variable.

  • Wrong direction: A countdown needs a negative Step. Confirm that the increment moves toward the end value.
  • Wrong boundary: Decide whether the first and last values should be included. For...Next includes a reached end value, so adjust the limits if the task needs a different range.
  • Counter type too small: An Integer can overflow when a count exceeds its range. Use Long for ordinary integer counters.
  • Counter changed in the body: Assigning a new value to the counter can make the flow hard to predict. Use a separate variable for calculations.
  • Missing recordset movement: A Do While Not rs.EOF loop must move to the next record. Without MoveNext, it can repeat the same row indefinitely.

To inspect a procedure, set a breakpoint by clicking beside a code line in the Visual Basic Editor, then run the code and step through it with F8 on Windows. On Mac, the Visual Basic Editor and shortcut availability can differ by Office version and keyboard settings; use the editor’s Debug menu if a Windows shortcut is unavailable. The Immediate window is useful for simple checks with Debug.Print.

Nested loops and Exit For

A loop can contain another loop, for example when comparing each item in one list with each item in another. Give each loop its own counter and indent the code so the structure is visible. A frequent logic issue is accidentally using the same counter variable for both loops, which makes it difficult to tell which loop controls the value.

Exit For leaves the innermost active For loop and continues with the statement after that loop. It is useful when a search finds the requested item before the full range has been checked. In nested loops, exiting the inner loop does not automatically exit the outer loop; add a separate condition or flag if the whole search should stop. Use early exits deliberately so they do not conceal incomplete processing.

Frequently Asked Questions

Does Access VBA have a For loop?
Yes. Access VBA supports For...Next for counter-based repetition and For Each...Next for visiting the items in a collection or array.

What is the difference between For and For Each?
For...Next advances a numeric counter across a range. For Each...Next assigns each item in a collection or array to an object or variable, which is convenient when you do not need a numeric index.

How do I loop through every record in an Access table?
Open a DAO or ADO recordset for the table or query, process the current record, and move to the next until EOF is true. For an action that applies uniformly to many records, consider an update query instead.

Can I use Exit For in Access VBA?
Yes. Exit For exits the current, innermost For loop. In nested loops, it does not by itself end every enclosing loop.

Why is my Access For loop running forever?
Check for a zero step, a counter being reset in the loop body, or a recordset loop that never calls MoveNext. A normal For...Next loop with a nonzero step and sensible bounds progresses automatically.

A practical way to choose and test a loop

Start by identifying what is being repeated: a known numeric range, each member of a collection, or records returned by a query. Choose the loop whose condition describes that task directly, declare the variables, and test a small case where you can predict the output. Check the boundary values and the number of iterations before connecting the code to an update or delete operation.

For database changes, keep the selection criteria explicit, use a backup, and verify the result after execution. A well-chosen loop should make the intended repetition obvious to another person reading the Access VBA, not merely make the code run.

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.