When you iterate through a collection of controls in a Microsoft Access form, the order of the list of controls that you end up with may not be sorted as you expect.  Rather than being sorted by the tab index, the list will be sorted by the order in which the controls were drawn on the form.

You can manually change the order by using the Bring to Front or Send to Back buttons on the ribbon when the form is in design view.  Or you can create your own collection of the form controls that is sorted using the tab order.  The VBA function below uses a two-step process to create that collection.

Step 1: The procedure puts the name of each control that has a tab index into the astrControlNames() array, using the tab index number as the element number in the array.  By nature, controls like labels will be excluded because they do not have a tab index.  This logic can be modified by setting the arguments TabStopOnly and VisibleOnly to True or False.  For example, if they were both set to True, a text box with Visible = No would not be included and a text box with Tab Stop = No would not be included.  The end result is an array with controls in tab index order.

Step 2: The procedure loops through the array and adds each control name to the custom control collection colControlsInOrder.

Here is an example of how to use the resulting collection of controls:

Public Function TestControlsInTabOrder(FormName As String)
' This procedure provides an example of how to use ControlsInTabOrder().
' It puts the visible controls of the Detail section that have a tab stop in
' tab index order and prints their names to the immediate window.
    
    Dim ctl As Control
    
    DoCmd.OpenForm FormName, acDesign
    
    For Each ctl In ControlsInTabOrder(Forms(FormName).Detail, True, True)
        Debug.Print ctl.Name
    Next ctl

    DoCmd.Close acForm, FormName

    Set ctl = Nothing
End Function

Code:

Public Function ControlsInTabOrder(FormSection As Access.Section, _
    TabStopOnly As Boolean, VisibleOnly As Boolean) As Collection
' This procedure returns a collection of controls from FormSection, sorted by
' tab order.
' If TabStopOnly = True, then only controls that have a tab stop are included.
' If VisibleOnly = True, then only controls that are visible are included.

' ControlsInTabOrder() Version 1.0.0
' Copyright © 2014 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
' I got the basic framework of this code from Jeremy Gollehon's post at:
' http://www.pcreview.co.uk/forums/looping-through-controls-tab-order-t2368340.html

On Error GoTo Err_Handler

    Dim astrControlNames() As String
    Dim colControlsInOrder As New Collection
    Dim ctl As Access.Control
    Dim lngElem As Long
    Dim blnInclude As Boolean
    
    ' Dimension the array to have an element for each control.
    ReDim astrControlNames(FormSection.Controls.Count - 1)
    
    ' Loop through all of the controls in the section.
    For Each ctl In FormSection.Controls
        ' Initialize the variable that will tell us later whether or not to
        ' include this control in the array.
        blnInclude = True
        ' Do not include if a tab stop is required and the control does not
        ' have one.
        If TabStopOnly And Not ctl.TabStop Then
            blnInclude = False
        End If
        ' Do not include if the control must be visible and it is not.
        If VisibleOnly And Not ctl.Visible Then
            blnInclude = False
        End If
        ' If the control should be included, then use the control name for
        ' the array element matching the tab index of the control.
        If blnInclude Then
            astrControlNames(ctl.TabIndex) = ctl.Name
        End If
    Next ctl
    
    ' Loop through the array of controls.  If a particular array element has
    ' a value, add it in order to the ordere collection of controls.
    For lngElem = 0 To UBound(astrControlNames)
        If astrControlNames(lngElem) <> "" Then
            colControlsInOrder.Add _
                FormSection.Controls(astrControlNames(lngElem))
        End If
    Next lngElem
    
    ' Pass back the control collection.
    Set ControlsInTabOrder = colControlsInOrder

Exit_Proc:
    On Error Resume Next
    Exit Function

Err_Handler:
    Select Case Err.Number
    Case 438
        ' The control does not support the TabStop property, like a label.
        Resume Next
    Case Else
        MsgBox Err.Number & " " & Err.Description, vbCritical, _
            "ControlsInTabOrder()"
        Resume Exit_Proc
    End Select
End Function

Download:

basControlsInTabOrder.zip