[AccessD] SPAM-LOW: Re: Form Controls - Class implementation

A.D.Tejpal adtp at airtelmail.in
Tue Oct 27 14:39:29 CDT 2009


JC,

    Thanks for providing a nice class based solution so promptly. Your well 
known command over classes deserves to be complimented.

    While conducting tests (Access 2003 desktop), it was found that listing 
of controls on form sections as displayed in the message box, is not always 
in keeping with the tab index. For example, if the tab index in form header 
is altered in design view and the form re-opened after saving, the results 
are not found consistent.

    It seems that while building the control collection in the class, 
greater reliability could be achieved by pouring the contents of a temporary 
class into the main one in the order of tab index.

    It would also appear that the extra round of iteration just for finding 
tab controls and their pages could be eliminated.

    Suggested modified versions incorporating the points outlined above are 
placed below, in the following sequence:

    (a) Class C_CtlsByTabIndex
    (b) Class C_CtlsByTabIndexMaster
    (c) Code in form's module

Best wishes,
A.D. Tejpal
------------

' Class C_CtlsByTabIndex
'=============================
Option Compare Database
Option Explicit

' 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
'-------------------------------------------

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
                ' 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).
                If ct.Parent Is fm Then
                    ' It is a control directly on the form
                    Cnt = Cnt + 1
                    colTemp.Add ct, CStr(Idx)
                End If

                ' If ct is a tab control, add its name to the
                ' comma separated list. (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 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:
    '   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
'=============================

' Class C_CtlsByTabIndexMaster
'=============================
Option Compare Database
Option Explicit

' 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
'-------------------------------------------

Function P_Init(fm As Access.Form)
    P_BuidClassCollection fm
End Function
'-------------------------------------------

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 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

'    Debug.Print Txt

    Set cObj = Nothing
End Property
'-------------------------------------------

Property Get prp_colC_CtlsByTabIndex() As Collection
    ' Return the entire collection of all instances of
    ' C_CtlsByTabIndex
    Set prp_colC_CtlsByTabIndex = _
                                    mcolC_CtlsByTabIndex
End Property
'-------------------------------------------

Function Fn_ObjC_CtlsByTabIndex(SecOrPgName As String) _
                                                        As C_CtlsByTabIndex
    ' Return 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
'=============================

' Code in form's module
'=============================
Option Compare Database
Option Explicit

' Declarations section
Public fc As C_CtlsByTabIndexMaster
'-------------------------------------------

Private Sub Form_Open(Cancel As Integer)
     Set fc = New C_CtlsByTabIndexMaster
     fc.P_Init Me
     MsgBox fc.prp_CtlNamesSortedByTbIndex
End Sub
'-------------------------------------------

Private Sub Form_Close()
     Set fc = Nothing
End Sub
'==================================


----- Original Message ----- 
From: jwcolby
To: Access Developers discussion and problem solving
Sent: Tuesday, October 27, 2009 00:58
Subject: Re: [AccessD] SPAM-LOW: Re: Form Controls - Class implementation


It does indeed get deep in a hurry.  The nice thing about a class 
implementation is that you can see
the answer in VBA English.

The code works BTW.

Going in I thought I was going to be able to pass the control collections as 
simple collection types
but not so.  I had to pass the control collections as objects, then use the 
objects as collections.
  I would love to know what type the control collections are.  Most likely 
below the covers they are
a strongly typed collection (of controls) but because they are at heart a 
collection all of the
methods and properties are there once the object is passed in.

The implementation does allow me to concentrate on objects that contain 
collections of controls
rather than on the controls themselves.

John W. Colby
www.ColbyConsulting.com


Kenneth Ismert wrote:
> Just read through the code. Very cool.
>
> It's surprising how much work it takes to get a complete answer to a
> seemingly trivial Access problem like "I want to see all my controls in 
> tab
> order".
>
> -Ken 




More information about the AccessD mailing list