Mastering Loops in VBA for Office Developers

Loops form the backbone of iterative programming in VBA for Microsoft Access, allowing developers to process recordsets, validate form inputs, or manipulate arrays efficiently without writing repetitive code. VBA provides four primary loop structures, Do loops (with While/Until variations), For Next, and For Each, each designed for specific scenarios such as conditional repetition or fixed-range traversal. Selecting the right loop depends on factors like whether the iteration count is known upfront, the need for early exits, or the data structure involved, such as DAO recordsets common in Access databases.

Do Loops

Do loops offer the most flexibility for scenarios where the number of iterations is not predetermined, making them ideal for tasks like scanning a query result until a condition is met or repeatedly prompting users via forms until valid input is received. The structure checks a condition either before or after each iteration, supporting both While (continue while true) and Until (continue until true) logic.

Do While Loop

Evaluates the condition at the start, skipping the loop entirely if false from the outset.

Example: Validating user input until a positive number is entered.

Dim iUserInput As Integer

Do While iUserInput <= 0
    iUserInput = Val(InputBox("Enter a positive number:"))
    If iUserInput = -1 Then Exit Do  ' User cancelled
Loop

Pros: Avoids unnecessary processing for invalid starting conditions, such as an empty recordset; highly efficient for pre-loop validations; supports clean Exit Do.
Cons: Risks infinite loops if the condition does not eventually falsify; lacks built-in iteration tracking.

Do Until Loop

Flips the logic, repeating until the condition becomes true. You don't see this technique as much, but it can be very useful at times.

Example: Processing a recordset until reaching the end or finding a specific customer.

Dim oRst As DAO.Recordset

Set oRst = CurrentDb.OpenRecordset("tblCustomers")
Do Until oRst.EOF
    If oRst!CustomerName = "SpecialClient" Then
        MsgBox "VIP customer found!"
        Exit Do
    End If
    Debug.Print oRst!CustomerName
    oRst.MoveNext
Loop
oRst.Close
Set oRst = Nothing

Pros: Natural phrasing for "keep going until finished"; intuitive for Access tasks; Exit Do provides immediate termination.
Cons: Still prone to infinite execution without precise condition design; requires careful state management.

Do Loop While or Do Loop Until

Checks post-iteration, ensuring at least one cycle regardless of the initial state.

Example: Prompting for confirmation until user agrees.

Dim sResponse As String

Do
    sResponse = InputBox("Type YES to continue:")
    If sResponse = "CANCEL" Then Exit Do
Loop Until sResponse = "YES"

Pros: Guarantees execution even if condition would otherwise prevent it; useful for user interactions with Exit Do escape hatch.
Cons: May perform extraneous work if condition fails immediately after first run, potentially causing side effects.

For Next Loops

For Next loops shine when dealing with a predictable number of iterations, such as stepping through an array of field values or a fixed set of form controls indexed from 1 to 10. Developers specify a counter variable, start and end values, and an optional Step increment, enabling forward or backward traversal with precise control.

Standard Form

Uses a counter.

Example: Summing values from an array of sales data with early exit.

Dim dSales(1 To 5) As Double
Dim dTotal As Double, iCtr As Integer

dSales(1) = 100: dSales(2) = 150: dSales(3) = 200: dSales(4) = 175: dSales(5) = 225
For iCtr = 1 To 5
    If dSales(iCtr) > 180 Then
        Debug.Print "High sale: " & dSales(iCtr)
        Exit For
    End If
    dTotal = dTotal + dSales(iCtr)
Next iCtr
Debug.Print "Total sales: " & dTotal

Pros: Offers exact iteration control; executes quickly on known bounds; Exit For provides clean early termination.
Cons: Breaks if bounds shift during execution; lacks graceful exits without Exit For.

Reverse Looping for Deletions: Why Use `Step -1` When Deleting Items

In Microsoft Access, deletion during iteration commonly occurs when working with form controls, list boxes, DAO recordsets and various other collections. These objects are position-sensitive, meaning their internal ordering changes as items are removed. Looping forward while deleting causes Access to skip elements or behave unpredictably.

Using a reverse For Next loop with Step -1 ensures that deletions do not interfere with the remaining items yet to be evaluated.

Forward Deletion Pitfall in Access

Imagine a list box containing five items. If you loop from index 0 upward and delete item 1, all remaining items shift up one position. The loop counter then advances, skipping the newly shifted item entirely.

As mentioned above, using the traditional forward loop in such cases will result in skipped elements and erroneous final results, hence why reversing the loop is critical!

Reverse Looping with `Step -1` (List Box Example)

This example removes all blank entries from a list box on an Access form.

Dim iCtr As Integer 

For iCtr = Me.lstItems.ListCount - 1 To 0 Step -1 
    If Nz(Me.lstItems.Column(0, iCtr), "") = "" Then 
        Me.lstItems.RemoveItem iCtr 
    End If 
Next iCtr

By looping backward, each deletion affects only items that have already been processed, preventing skipped rows.

Form Control Deletion Example

Removing dynamically created controls from a form requires reverse iteration because the Controls collection reindexes itself after each deletion.

Dim iCtr As Integer 

For iCtr = Me.Controls.Count - 1 To 0 Step -1 
    If Me.Controls(iCtr).Tag = "TempControl" Then 
        Me.Controls.Remove Me.Controls(iCtr).Name 
    End If 
Next iCtr

DAO Recordset Deletion with Reverse Traversal

Although DAO recordsets don’t use numeric indices, the same reverse-iteration principle applies. Positioning the cursor at the last record and moving backward ensures safe deletion.

Dim oRst As DAO.Recordset 

Set oRst = CurrentDb.OpenRecordset("tblOrders", dbOpenDynaset) 
If oRst.RecordCount > 0 Then 
    oRst.MoveLast 
    Do While Not oRst.BOF 
        If oRst!OrderAmount <= 0 Then 
            oRst.Delete 
        End If 
        oRst.MovePrevious 
    Loop 
End If 
oRst.Close 
Set oRst = Nothing

When `Step -1` Is Mandatory in Access

Use reverse looping whenever you:

Delete items from list boxes or combo boxes
Remove form controls dynamically
Modify collections where Access reindexes automatically

Key Takeaway

In Access VBA, forward loops and deletions do not mix. A For Next loop with Step -1 protects against index shifting and guarantees that every item is evaluated. When in doubt, reverse the loop—especially in UI-driven Access applications.

Combo Box Search

Example: Finding a specific value in a form's combo box rows.

Dim iCtr As Integer

For iCtr = 0 To Me.cboTarget.ListCount - 1
    If Me.cboTarget.Column(0, iCtr) = "SomeTargetValue" Then
        MsgBox "Found at index " & iCtr
        Exit For
    End If
Next iCtr

For Each Loops

For Each simplifies iteration over collections or arrays without manual indexing, making it perfect for Access objects like form controls. It automatically handles dynamic sizes, focusing on the "each item" logic rather than positions.

Form Control Processing

This loop reads items sequentially but does not support modification or reversal natively.

Example: Clearing all textboxes on a form with early exit.

Dim oCtl As Control

For Each oCtl In Me.Controls
    If oCtl.ControlType = acTextBox Then
        oCtl.Value = ""
        If oCtl.Name = "txtCriticalField" Then
            MsgBox "Critical field cleared"
            Exit For
        End If
    End If
Next oCtl

Pros: Highly readable with no index maintenance; adapts to varying collection sizes; Exit For works cleanly.
Cons: Items are read-only; generally slower on massive datasets; no reverse iteration or indexed access.

Loop Comparison Table

Loop Type	Ideal Scenario	Performance	Early Exit	Reverse Loop	Dynamic Sizes
Do While/Until	Unknown iterations	Fast	Exit Do	No	Yes
For Next	Fixed ranges/arrays	Fastest	Exit For	Yes (Step -1)	No
For Each	Collections/objects	Moderate	Exit For	No	Yes

Mastering these loops involves pairing them with Access-specific best practices. Use On Error Resume Next for robust recordset navigation and always implement Exit statements for user cancellation. Reverse For Next loops, use of a Step -1, are mandatory when deleting to avoid index skipping. Test thoroughly in the Immediate Window with Debug.Print to verify behavior, especially with recordset deletions.

-- This article remains a work in process --

Page History

Date	Summary of Changes
2026-02-02	Initial Release