jwcolby
jwcolby at colbyconsulting.com
Tue Oct 27 20:30:10 CDT 2009
A.D., Let me first say that I probably wouldn't make the effort to prevent the extra control collection iterations because it causes code (concept) complexity out of proportion with the problem it solves. The code that scans the control collections is so fast that it doesn't make sense to add that complexity. That said, if I were going to try and prevent the extra control collection iterations I would attack it from a different angle. Classes should reflect the objects they represent. I chose initially to simply make the clsControlSort represent simply sorted collections of controls. I put the collection iteration code in that class on the "code close to the objects manipulated" concept. As you point out, that does cause the same set of controls to be "found" two times IF on a tab, since it will be found in the section iteration and in the tab iteration. In the end, I don't really care, the code is fast enough that the extra complexity just isn't worthwhile to fix the "problem" (IMHO). Should I attempt to do the "single iteration of the control collection" thing I would probably change the clsCtlsSorted to simply have a collection to sorted controls in and a property to get /set controls into the collection. Perhaps a "sort the collection" method as well. Now the iterator up in the supervisor class would "discover" the parent (as yours does) but I would instantiate clsCtlsSorted for each object. One for each section, and one for each tab page encountered. Now as a control's parent is discovered, the method to store the control in the correct clsCtlsSorted would be called and the control placed in thhe correct clsCtlsSorted. Basically the form's controls collection would be iterated and each and every control examined and placed in the correct clsCtlsSorted instance. Certainly doable. John W. Colby www.ColbyConsulting.com A.D.Tejpal wrote: > 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 >