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