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