locked
Ordering Controls in the controls collection by Tab Order RRS feed

  • Question

  • I'm using

        For Each ctl In frm.Controls

    to validate data.

    Is there a way to order the Controls by Tab order?

    I realise, of course, that some controls like labels will not have a tab order but They are irrelevant to this function.


    Kent
    Monday, September 5, 2011 11:34 PM

Answers

  • My suggestion would be to loop through the controls like you are doing and collect the tab index and control name, of each control, into a two dimensional array (or two one dimensional arrays) and then sort the array by tab order.

    Or do the same thing into a temp table via a recordset, then sort the recordset by tab order and loop through it.

    • Marked as answer by KentGorrell Tuesday, September 6, 2011 2:29 AM
    Tuesday, September 6, 2011 12:16 AM

All replies

  • AFAIK, there's no way to control the order in which the controls are presented in the collection. 
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Monday, September 5, 2011 11:48 PM
  • My suggestion would be to loop through the controls like you are doing and collect the tab index and control name, of each control, into a two dimensional array (or two one dimensional arrays) and then sort the array by tab order.

    Or do the same thing into a temp table via a recordset, then sort the recordset by tab order and loop through it.

    • Marked as answer by KentGorrell Tuesday, September 6, 2011 2:29 AM
    Tuesday, September 6, 2011 12:16 AM
  • Doug - I guess you're right.

    Alphonse - interesting work around. Some form of this method looks like the way to go.


    Kent
    Tuesday, September 6, 2011 2:28 AM
  • Handling of form controls in the order of tab index.
    ==================================
     
    Kent,
     
        You wish to handle controls on a form, in the order of their respective tab index. For a form section without tab controls, it would be a simple matter to build up an array (or collection) holding the names of controls in such a manner that array index (or key of collection element) matches the tab index. Such an array does not require any further sorting.
     
        In a typical form, having multiple sections and tab controls, adoption of collections would appear to be the optimum approach. At first sight, the number of potential collections might look rather too large. A form having three tab controls each with 4 pages, would need 15 collections as each form section or tab control page has its own set of tab indices for controls located therein (starting at 0 in each case).

        However, there is an interesting way to handle this requirement conveniently by adoption of a single collection where each of its elements is itself a collection, permitting a generic subroutine to handle unlimited number of tab control pages apart from the three form sections (detail, header & footer)

        Still better, the whole requirement can be covered by a set of two classes as given below (at the end of this post):

        (a) Class C_CtlsByTabIndexMaster:
        -------------------------------------
              It represents a collection of class objects conforming to class C_CtlsByTabIndex
     
        (b) Class C_CtlsByTabIndex:
        ------------------------------
              It represents a collection of controls for the given form section or tab control page, arranged in the order of tab index.
     
        Sample Usage:
        ---------------
        Based upon the two classes mentioned above, typical code in form's module would be as follows:
     
    ' Code in form's module:
    '==========================
    ' Declarations section
    Public fc As C_CtlsByTabIndexMaster
    '---------------------------------------
     
    Private Sub Form_Open(Cancel As Integer)
         Set fc = New C_CtlsByTabIndexMaster
         fc.P_Init Me
    End Sub
    '---------------------------------------
     
    Private Sub Form_Close()
         Set fc = Nothing
    End Sub
    '==========================
     
        With the arrangement outlined above, sample statement as given below, called from anywhere in form's module, will display in immediate window, the names of all controls in the order of tab index, separately for each form section and tab control page.
     
    ' Sample call in form's module
    '------------------------------------------------
    Debug.Print fc.prp_CtlNamesSortedByTbIndex
    '------------------------------------------------
     
        Other properties and functions included in the sample classes can be made use of as needed.
       
    Best wishes,
    A.D. Tejpal
    ------------
     
    ' C_CtlsByTabIndexMaster  
    ' (Name of Class Module)
    '=============================
    ' Declarations section
     
    ' Collection of class objects, each representing
    ' a collection of controls sorted as per tab index.
    Private mcolC_CtlsByTabIndex As Collection
    '--------------------------------------------
     
    Private Sub Class_Initialize()
        Set mcolC_CtlsByTabIndex = New Collection
    End Sub
    '--------------------------------------------
     
    Private Sub Class_Terminate()
        Set mcolC_CtlsByTabIndex = Nothing
    End Sub
    '--------------------------------------------
     
    Public Sub P_Init(fm As Access.Form)
        P_BuidClassCollection fm
    End Sub
    '--------------------------------------------
     
    Private Sub P_BuidClassCollection(fm As Access.Form)
        On Error Resume Next
        Dim cObj As C_CtlsByTabIndex
        Dim pg As Access.Page
        Dim Cnt As Long, Ctr As Long
        Dim Rtv As Variant
       
        ' Max possible 5 sections in a form (0 - acDetail,
        ' 1 - acHeader, 2 - acFooter, 3 - acPageHeader,
        ' 4 - acPageFooter)
        ' Note - On a form, PageHeader & PageFooter
        '            come into play only if the form is printed.
        For Cnt = 0 To 4
            Err.Clear
            If Not IsError(fm.Section(Cnt).Controls.Count _
                                                                    > 0) Then
                ' The section exists. Add an instance of
                ' class C_CtlsByTabIndex for this section
                ' to collection mcolC_CtlsByTabIndex
                Set cObj = New C_CtlsByTabIndex
                cObj.P_Init fm, fm.Section(Cnt)
                mcolC_CtlsByTabIndex.Add cObj, _
                                        fm.Section(Cnt).Name
               
                ' Take similar action for tab control pages
                ' - if any - on this section.
                '------------------------------------------------
                ' Get comma separated list of tab control
                ' names on this section
                Rtv = cObj.prp_TabControlList
                If Len(Rtv) > 0 Then
                    ' Build array of tab control names
                    Rtv = Split(Rtv, ",")
                    For Ctr = 0 To UBound(Rtv)
                        ' Add an instance of class
                        ' C_CtlsByTabIndex for each page to
                        ' collection mcolC_CtlsByTabIndex
                        For Each pg In fm(Rtv(Ctr)).Pages
                            Set cObj = New C_CtlsByTabIndex
                            cObj.P_Init fm, pg
                            mcolC_CtlsByTabIndex.Add cObj, _
                                                                    pg.Name
                        Next
                    Next
                End If
             End If
        Next
       
        Set pg = Nothing
        Set cObj = Nothing
       
        On Error GoTo 0
    End Sub
    '--------------------------------------------
     
    Property Get prp_CtlNamesSortedByTbIndex() As String
        ' Returns a list of all controls on various sections
        ' and pages in the order of tab index
        Dim cObj As C_CtlsByTabIndex
        Dim Txt As String
     
        For Each cObj In mcolC_CtlsByTabIndex
            Txt = Txt & IIf(Len(Txt) > 0, vbCrLf, "") & _
                    cObj.prp_CtlNamesSortedByTbIndex
        Next
       
        prp_CtlNamesSortedByTbIndex = Txt
       
        Set cObj = Nothing
    End Property
    '--------------------------------------------
     
    Property Get prp_colC_CtlsByTabIndex() As Collection
        ' Returns the entire collection of all instances of
        ' C_CtlsByTabIndex
        Set prp_colC_CtlsByTabIndex = _
                                        mcolC_CtlsByTabIndex
    End Property
    '--------------------------------------------
     
    Public Function Fn_ObjC_CtlsByTabIndex( _
                                SecOrPgName As String) _
                                As C_CtlsByTabIndex
        ' Returns an instance of class C_CtlsByTabIndex for a
        ' specific form section or tab page.
        ' (SecOrPgName is the name of the form section or
        ' tab page)
        On Error Resume Next
        Set Fn_ObjC_CtlsByTabIndex = _
                        mcolC_CtlsByTabIndex(SecOrPgName)
    End Function
    '=============================
     
    ' C_CtlsByTabIndex 
    ' (Name of Class Module)
    '=============================
    ' Declarations section
     
    ' form section or tab control page
    Private msc As Object
    ' Collection of controls sorted as per tab index.
    Private mcolCtls As Collection
    ' Comma separated list of tab controls on this section
    Private mTabCtList As String
    '--------------------------------------------
     
    Private Sub Class_Initialize()
        Set mcolCtls = New Collection
    End Sub
    '--------------------------------------------
     
    Private Sub Class_Terminate()
        Set mcolCtls = Nothing
        Set msc = Nothing
    End Sub
    '--------------------------------------------
     
    Public Sub P_Init(fm As Access.Form, sc As Object)
        Set msc = sc
        P_StoreControls fm, sc
    End Sub
    '--------------------------------------------
     
    Private Sub P_StoreControls( _
                    fm As Access.Form, sc As Object)
        On Error Resume Next
        ' Builds a temp collection of controls keyed
        ' as per tab index and transfers its contents
        ' to mcolCtls in the order of tab index.
       
        ' sc represents either a form-section or tab
        ' control page
        Dim colTemp As Collection
        Dim ct As Access.Control
        Dim Idx  As Long
        Dim Cnt As Long
       
        Set colTemp = New Collection
        mTabCtList = ""
        Cnt = 0
        For Each ct In sc.Controls
            Err.Clear
            Idx = ct.TabIndex
            If Err.Number = 0 Then
                ' The control has tab index
                If sc.Parent Is fm Then
                    ' It means sc is a form section.
                    ' Cycle through all controls on this section
                    ' (When the argument to this subroutine
                    ' is a form section, all controls belonging
                    ' to tab control pages are to be ignored as
                    ' there is independent set of tab indices
                    ' for each tab page. In this set, include only
                    ' the controls directly located on the form).
                    If ct.Parent Is fm Then
                        ' It is a control directly on the form.
                        ' Add to the collection.
                        Cnt = Cnt + 1
                        colTemp.Add ct, CStr(Idx)
                    End If
                   
                    ' If ct is a tab control, add its name to the
                    ' comma separated list of tab controls.
                    ' (This way, we avoid an extra round of
                    ' scanning in the master class just for finding
                    ' the tab control pages)
                    If ct.ControlType = acTabCtl Then
                        mTabCtList = mTabCtList & "," & ct.Name
                    End If
                Else
                    ' sc itself is a tab control page - no need for any
                    ' further check.
                    Cnt = Cnt + 1
                    colTemp.Add ct, CStr(Idx)
                End If
            End If
        Next
       
        ' Remove leading comma from TabCtList
        If Len(mTabCtList) > 1 Then
            mTabCtList = Mid(mTabCtList, 2)
        End If
       
        ' Transfer the contents of temp collection to
        ' mcolCtls in the order of tab index
        If colTemp.Count > 0 Then
            For Cnt = 0 To colTemp.Count - 1
                mcolCtls.Add colTemp(CStr(Cnt)), CStr(Cnt)
            Next
        End If
       
        ' Note:
        '   Controls on a tab page feature in the controls
        '   collection for the page as well as that for the
        '   form section on which the tab control is located.
       
        '   The parent for controls on tab control page is
        '   the relevant page. For all other controls, it is
        '   the form.
       
        '   Section property for all controls on a given
        '   section is identical, (irrespective of the fact
        '   whether the control is located directly on the
        '   form or on a tab control page).
     
        Set ct = Nothing
        Set colTemp = Nothing
        On Error GoTo 0
    End Sub
    '--------------------------------------------
     
    Property Get prp_TabControlList() As String
         prp_TabControlList = mTabCtList
    End Property
    '--------------------------------------------
     
    Property Get prp_SecPageName() As String
         prp_SecPageName = msc.Name
    End Property
    '--------------------------------------------
     
    Property Get prp_ControlByTabIndex(TbIndex _
                                As Long) As Access.Control
        ' Returns specific control having given tab index
        Set prp_ControlByTabIndex = mcolCtls(CStr(TbIndex))
    End Property
    '--------------------------------------------
     
    Property Get prp_CtlNamesSortedByTbIndex() As String
        ' Iterate the collection of controls getting each
        ' control name and appending it to a string
        Dim ct As Access.Control
        Dim Txt As String
       
        Txt = msc.Name & " (Tot tab indexed controls = " & _
                                mcolCtls.Count & ") : " & vbCrLf
        For Each ct In mcolCtls
            Txt = Txt & vbTab & ct.Name
        Next
       
        prp_CtlNamesSortedByTbIndex = Txt
       
        Set ct = Nothing
    End Property
    '--------------------------------------------
     
    Property Get prp_ControlsByTabIndex() As Collection
        ' Return the collection of controls sorted by tab index
        ' (and keyed on TabIndex)
        Set prp_ControlsByTabIndex = mcolCtls
    End Property
    '--------------------------------------------
     
    Property Get prp_TabIndexedControlsCount() As Long
        ' Return the count of tab indexed controls on
        ' this section or page
        prp_TabIndexedControlsCount = mcolCtls.Count
    End Property
    '=============================
     
    ----- Original Message -----
    Newsgroups: Msdn.en-US.accessdev
    Sent: Tuesday, September 06, 2011 05:04
    Subject: Ordering Controls in the controls collection by Tab Order

    I'm using

        For Each ctl In frm.Controls

    to validate data.

    Is there a way to order the Controls by Tab order?

    I realise, of course, that some controls like labels will not have a tab order but They are irrelevant to this function.


    Kent

    A.D. Tejpal
    Tuesday, September 6, 2011 5:00 PM