DataBase - Enumerating controls

Asked By Kirk Wilson

07-Feb-10 11:52 AM

I found a code snippet in help that lists all controls on a tab control.

For Each ctlCurrent In tabCtl
Debug.Print ctlCurrent.Name
Next ctlCurrent

I modified the code to return a control by its index

For I = 0 To 15
Debug.Print tabCtl!ctlCurrent(I).Name
Next I

This creates a type mismatch runtime error

How do I correct this error? Full code listing follows:

frmMonth24 is the main form.
tabForm is a tabcontrol on frmMonth24

Public Function GetPageName()

Dim tabCtl As TabControl
Dim ctlCurrent As Control
Dim I As Integer

On Error GoTo ErrorHandler

' Return reference to tab control on Employees form.
Set tabCtl = Forms!frmMonth24!tabForm

' List all controls on the tab control in the Debug window.
'   For Each ctlCurrent In tabCtl
'      Debug.Print ctlCurrent.Name
'   Next ctlCurrent
For I = 0 To 15
Debug.Print tabCtl!ctlCurrent(I).Name
Next I

Set tabCtl = Nothing
Set ctlCurrent = Nothing

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Function

07-Feb-10 12:02 PM

I believe the syntax ought to be:

Debug.Print tabCtl(I).Name

IOW, you do not need the ctlCurrent when you are accessing a collection by
its index/key unlike the For Each...

07-Feb-10 12:47 PM

Your modified code also creates a type mismatch runtime error.

08-Feb-10 03:08 AM

I do not understand your code.

A tab control has no controls collection and it has no default
collection, either, so I cannot see how any of your code ever worked.

This code:

For Each ctlCurrent In tabCtl
Debug.Print ctlCurrent.Name
Next ctlCurrent

...would have to have been:

Dim ctlPage as Control
Dim ctlCurrent As Control

For Each ctlPage In Forms!frmMonth24!tabForm.Pages
For Each ctlCurrent In ctlPage.Controls
Debug.Print ctlCurrent.Name
Next ctlCurrent
Next ctlPage
Set ctlCurrent = Nothing
Set ctlPage = Nothing

There is really no need to navigate by index unless you have keyed
control names to their index number (which is inadvisable in most
cases, but not completely out of bounds). In that case, you would  do
this:

Dim ctlPage as Control
Dim i As Integer

For Each ctlPage In Forms!frmMonth24!tabForm.Pages
For i = 0 To ctlPage.Controls.Count - 1
Debug.Print ctlPage.Controls(i).Name
Next ctlCurrent
Next ctlPage
Set ctlPage = Nothing

But as you can see, it is actually more efficient to use the object
control instead of the index.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

08-Feb-10 12:48 AM

I appreciate your reply but you have missed my point. I do not know the
subform name in advance. I need to programatically determine the subform
name so that I can determine it is record source to build an SQL statement
to populate the subform at runtime with different where clauses depending
on the conditions at run time. A tab control may not have a controls
collection but it does have pages and on those pages are controls
(subforms) with properties. I needed a general purpose procedure to run
on many tab controls with many subforms to determine those properties
during execution. The reason to navigate by index is to determine the
subform name for a particular but changing tabcontrol. Once the subform
name is determined then I can refer to the properties explicitly. IT
looks like your last snippet is what I need. I hope it works.

Thanks for the help Kirk

08-Feb-10 11:24 AM

Oddly enough, the original code does work, at least in Access 2003;  I
tested it.  I do not know why.  I cannot figure out what collection the For
Each is iterating through.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

08-Feb-10 11:48 AM

Same here. I actually never knew that it could be done until OP
mentioned that he cited from a help file. Unforunately, help file on
TabControl collection (yes, a collection, not a control. At least if the
help file is to be believed) was quite terse and cryptic. However, while
it does enumerate controls on a tab, it also enumerates pages as well so...

As for the OP's question about using For...Next, I am not sure what else
to suggest since the syntax I suggested should have worked neither could
I find an explicit collection within the tab control. I'd probably use
nested loops over the pages then its controls collection if only because
it is better documented and will not get pages enumerated which I do not
think you want that anyway.

09-Feb-10 12:51 AM

I could not get it to work in A2003. I would have written a different
post if I had (I would have suggested explicitly using the Pages
collection instead of doing it implicitly).

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

09-Feb-10 01:01 AM

My post was not intended to address that in any way. I was just
addressing the fact that the code you provided for navigating a tab
control could not possibly work.

(though it seems to work for Dirk, I could not get it to work)


No, you do not need to use an index for that.

Public Sub ListSubFormsOnTabControl(frm As Form)
Dim ctl As Control
Dim ctlPage As Control
Dim ctlOnPage As Control

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTabCtl
For Each ctlPage In ctl.Pages
For Each ctlOnPage In ctlPage.Controls
Select Case ctlOnPage.ControlType
Case acSubForm
Debug.Print ctlOnPage.Name
End Select
Next ctlOnPage
Next ctlPage
Set ctlPage= Nothing
End Select
Next ctl
Set ctl = Nothing
End Sub

Indeed, in a lot of cases where you might try to use a counter, it is
actually simpler to use a variant. Where you might have this (where
col is some collection):

For i = 0 To col.Count
Debug.Print col(i).Name
Next i

...can be replaced with this:

For Each varItem In col
Debug.Print col(varItem).Name
Next i

You save setting up the range of the counter.


The ListSubFormsOnTabControl() sub above does exactly what you asked
for without using any counters at all.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

09-Feb-10 01:23 AM

OK, I see why I could not get it to work -- I did not realize there
was a dedicated TabControl object type and was defining my tab as a
regular control. This code works, and is much simpler than the other
code I posted earlier:

Public Sub EnumerateTabSubForms(ctlTab As TabControl)
Dim ctl As Control

For Each ctl In ctlTab
If ctl.ControlType = acSubform Then
Debug.Print ctl.Name
End If
Next ctl
End Sub

That's pretty simple. I have never needed to enumerate the controls on
a tab control, but this would be handy if I did.

My big annoyance about the relationship of tab controls and their
controls is that the parent of a control on a tab control is the TAB
PAGE. This causes issues when I want to know if a form is loaded as
a subform -- I have a few forms that are loaded normally but also as
subforms and while I could check if the form is in the Forms
collection, that would break if the form happened to be open on its
own while it is also loaded as a subform.

Oops. I see I have already solved that issue -- here is my old
IsSubForm() function:

Public Function IsSubFormOld(frm As Form) As Boolean
On Error GoTo errHandler

IsSubFormOld = Len(frm.Parent.Name) > 0

exitRoutine:
Exit Function

errHandler:
Select Case err.Number
Case Is <> 2452
MsgBox err.Number & ": " & err.Description, vbExclamation, _
End Select
Resume exitRoutine
End Function

I also note that there is no problem if the parent is a tab page or
not -- I am obviously misremembering where this was an issue!

I replaced that with this:

Public Function IsSubForm(frm As Form) As Boolean
On Error GoTo errHandler
Dim frmLoop As Form
Dim bolIsMainForm As Boolean

For Each frmLoop In Forms
If frmLoop.Name = frm.Name Then
If frmLoop.hWnd = frm.hWnd Then
bolIsMainForm = True
Exit For
End If
End If
Next frmLoop
Set frmLoop = Nothing
IsSubForm = Not bolIsMainForm

exitRoutine:
Exit Function

errHandler:
MsgBox err.Number & ": " & err.Description, vbExclamation, _
Resume exitRoutine
End Function

That takes care of my earlier objection, because it compares the
window handles (I forgot that I'd written this code!).

Anyway, the place where the parent issue is a problem is when I need
to know the parent form of a control. I have this very ugly line in
one of my apps:

Set frm = ctl.Parent.Parent.Parent

Ack. That makes my teeth hurt. It is, fortunately, used in a
situation where I know for sure that the control it is acting on is
on a tab control, so there is no danger of it breaking. But I
hesitate to think how awful it would be if the control were on a
subform that was on a tab. I think it would be:

Set frm = ctl.Parent.Parent.Parent.Parent.Parent.Parent