Enumerating controls

[Kirk Wilson]

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

 

[Banana]

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

I believe the syntax ought to be:

Debug.Print tabCtl(I).Name

IOW, you don't need the ctlCurrent when you're accessing a collection by
its index/key unlike the For Each...

 

[Kirk Wilson]

On Sun, 07 Feb 2010 09:02:55 -0800, Banana wrote:

Your modified code also creates a type mismatch runtime error.

 

[David W. Fenton]

Your modified code also creates a type mismatch runtime error.

I don't understand your code.

A tab control has no controls collection and it has no default
collection, either, so I can't 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've keyed
control names to their index number (which is inadvisable in most
cases, but not completely out of bounds). In that case, you'd 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's actually more efficient to use the object
control instead of the index.

 

[Kirk Wilson]

I appreciate your reply but you have missed my point. I don't know the
subform name in advance. I need to programatically determine the subform
name so that I can determine it's 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

 

[Dirk Goldgar]

I don't understand your code.

A tab control has no controls collection and it has no default
collection, either, so I can't 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

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

 

[Banana]

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

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'm 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's better documented and won't get pages enumerated which I don't
think you want that anyway.

 

[David W. Fenton]

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

I couldn't 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]

I appreciate your reply but you have missed my point. I don't know
the subform name in advance. I need to programatically determine
the subform name...

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 couldn't get it to work)

... so that I can determine it's 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.

No, you don't 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's
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.

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.

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

 

[David W. Fenton]

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

OK, I see why I couldn't get it to work -- I didn't 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've 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's also loaded as a subform.

Oops. I see I've already solved that issue -- here's 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, _
"Error in IsSubFormOld()"
End Select
Resume exitRoutine
End Function

I also note that there's no problem if the parent is a tab page or
not -- I'm 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, _
"Error in IsSubForm()"
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's 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

I guess I could shortcurcuit this using variables of TabControl
type, but I'd be skipping only one level (the Pages collection).

Anyway, I've nattered on far too long at this point and I'm sure
nobody is any longer paying any attention...