Seeking Elegant Solution for Traversing Control Hierarchy to Find Parent Form

[Uncle Gizmo]

### Subject: Seeking Elegant Solution for Traversing Control Hierarchy to Find Parent Form

Hi All...
I've been working on a VBA function to traverse the hierarchy of controls in Microsoft Access to find the parent form. While I have a working solution, I'm aiming for a more elegant and streamlined approach. Here’s the scenario:

### The Challenge
I need to traverse the hierarchy of controls to find the top-level form. My current solution involves checking the name of each parent object and verifying it against the forms collection to avoid type mismatch errors (error 13) and invalid reference errors (runtime error 2452). Here’s the working code:

' Function to get the parent form of a control
Private Function fGetParentForm(ctrlActiveControl As Control) As Form
    Dim ctlToTest As Control
    Set ctlToTest = ctrlActiveControl
    Dim X As Integer

    For X = 1 To 20
        If Not fParentIsaForm(ctlToTest.Parent.Name) Then
            Set ctlToTest = ctlToTest.Parent
        Else
            Set fGetParentForm = ctlToTest.Parent
            Exit Function
        End If
    Next X

    If X >= 20 Then
        MsgBox "ERROR 20 Levels EXCEEDED, change 20 to a higher figure", vbExclamation, conAppName
        Set fGetParentForm = Nothing
    End If
End Function

' Function to check if the parent is a form
Private Function fParentIsaForm(strIsaForm As String) As Boolean
    fParentIsaForm = False
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    For Each obj In dbs.AllForms
        If obj.Name = strIsaForm Then
            fParentIsaForm = True
            Exit For
        End If
    Next obj
End Function

While this approach works, it feels a bit clunky and verbose. I’m looking for a more elegant solution that can dynamically handle any depth of control hierarchy without hitting type mismatch or invalid reference errors.

### Attempted So Far
- I’ve tried using a `Do While` loop with `TypeName`, but encountered runtime errors and type mismatches.
- Variations of using `Object` types instead of `Control`, but these also resulted in errors or failed to address the core issue.

Is there a better way to traverse the control hierarchy in VBA to find the top-level form? I’m looking for a solution that avoids the pitfalls of type mismatches and invalid references, ideally with a touch of elegance.

TIA

Thank you in advance for your expertise and time.

Cheers Tony - Uncle Gizmo

 

[MajP]

Public Function getParentForm(callingCtl As Access.Control) As Access.Form
  On Error GoTo errlbl
  Dim ctl As Object
  Set ctl = callingCtl.Parent
  Do While Not TypeOf ctl Is Access.Form
    Set ctl = ctl.Parent
  Loop
  Set getParentForm = ctl
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Function

If you mean the top level form not the immediate parent

Public Function getMainForm(callingCtl As Access.Control) As Access.Form
  On Error GoTo errlbl
  Dim ctl As Object
  Set ctl = callingCtl.Parent
  Do While Not InForms(ctl)
    Set ctl = ctl.Parent
  Loop
  Set getMainForm = ctl
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Function

Public Function InForms(ctl As Object) As Boolean
  Dim frm As Access.Form
  If TypeOf ctl Is Access.Form Then
    For Each frm In Forms
      If frm Is ctl Then
        InForms = True
        Exit Function
      End If
    Next frm
  End If
 
End Function

 

[Uncle Gizmo]

Code:Copy to clipboard

Public Function getParentForm(callingCtl As Access.Control) As Access.Form
  On Error GoTo errlbl
  Dim ctl As Object
  Set ctl = callingCtl.Parent
  Do While Not TypeOf ctl Is Access.Form
    Set ctl = ctl.Parent
  Loop
  Set getParentForm = ctl
  Exit Function
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Function

Thank you, MajP, for your excellent solution - Your first block of code – it works a treat!

I'm a bit miffed because, as far as I can tell, I've tried everything you've used in your solution. However, I believe it's the subtle nuances that made all the difference and caused my code to fail. It's very late here, so I'm off to bed, but tomorrow I'll look back through my notes to see if I can pinpoint where I went wrong.

Thanks again for your help – your coding skills are truly top-notch!

 

[Uncle Gizmo]

If you mean the top level form not the immediate parent

My wording could have been better! I just wanted the form that the control sits on. Could be the top level Form or could be a subform...

 

[MajP]

However, I believe it's the subtle nuances that made all the difference and caused my code to fail.

I originally struggled with this code because it was not intuitive to me that the parent of a control on a form is not always the form. It is strange that a control on a tab control has the page as its parent and the page has the tab control as the parent. Even though the control is a member of the form's controls collection. It is also a member of the page's controls collection.
So the trick is that a parent can be either a form object or a control object and that is why you have to use a generic "Object" variable in your code and then test it to see what type of object it is.

 Dim ctl As Object

 

[Uncle Gizmo]

It is strange that a control on a tab control has the page as its parent and the page has the tab control as the parent. Even though the control is a member of the form's controls collection. It is also a member of the page's controls collection.

Yes I noticed that about tab controls .. very peculiar!

 

[MarkK]

Here's my stab at these problems. Both functions recursively crawl up the Control/Page/Tab/Form hierarchy using the Parent property, which all members expose.
- The MainForm is the first object that doesn't have a Parent
- The ParentForm is the first Access.Form we encounter above the object in question.

Function GetMainForm(obj As Object) As Access.Form
On Error GoTo handler
    Set GetMainForm = GetMainForm(obj.Parent)           ' MainForm has no parent
    Exit Function
handler:
    Set GetMainForm = obj
End Function

Function GetParentForm(obj As Object) As Access.Form
    If TypeOf obj.Parent Is Access.Form Then
        Set GetParentForm = obj.Parent                  ' ParentForm is the first form we encounter
    Else
        Set GetParentForm = GetParentForm(obj.Parent)
    End If
End Function

One thing that is interesting to me is that this line...

Set GetMainForm = GetMainForm(obj.Parent)

...doesn't raise an error if obj is a Button, and obj.Parent is a Page, because GetMainForm() is strongly typed as Access.Form. It must be because it is a recursive function, and/or because the objects are not strongly typed, and so the value of GetMainForm() never resolves as a Page or a Tab. It's only when the recursion hits its anchor that a value is assiged to the function, and at that time the object returned is always an Access.Form, and then each instance of recursion hands that Form object back to its caller.

 

[CJ_London]

Even though the control is a member of the form's controls collection. It is also a member of the page's controls collection.

It is also a member of a section controls collection

 

[MarkK]

It is also a member of a section controls collection

What matters though is what the control renders as its Parent. I could make a CollectionA and put controls in it, but that doesn't make my CollectionA the Parent of the control.