Get Top Form By Control

How do you return the top form for an arbitrary control instead of the first subform that comes your way?

Get Top Form By Control

In my article, Get Form By Control, I used recursion and the TypeOf...Is expression to find the first parent form of a control.  If the control happens to be on a subform, then the GetFormByCtl() function will return that form.  

But what if you specifically want to find the top form (i.e., the outermost form that is not a subform of some other form)?

Subform controls

Let's create a new blank form named TopForm.  Next, we'll add a subform control named sfMyForm and set its source object property to MyForm.

The first thing I should point out is that the SubForm control and the subform's source form are two different objects:

?TypeName(Form_TopForm.sfMyForm)
SubForm

?TypeName(Form_TopForm.sfMyForm.Form)
Form_MyForm

Subform checks

When checking to see if a given control is a subform, there are several ways we can perform the check:

  • TypeOf ... Is SubForm
  • .ControlType = acSubform
  • TypeName(...) = "SubForm"
?TypeOf Form_TopForm.sfMyForm Is SubForm
True

?Form_TopForm.sfMyForm.ControlType = acSubform
True

?TypeName(Form_TopForm.sfMyForm) = "SubForm"
True

Form genealogy

Unfortunately, none of these checks helps us when we are trying to find the top form from some arbitrary control.  If we track a control's ancestry by repeatedly calling its parent, grandparent, great-grandparent, etc., we will find that while Access will return a subform's form object, it will completely skip over the subform control object itself.

For example:

?Form_TopForm.sfMyForm.Form.Controls(0).Name
MyTabCtl

?Form_TopForm.sfMyForm.Form.Controls(0).Parent.Name
MyForm

?Form_TopForm.sfMyForm.Form.Controls(0).Parent.Parent.Name
TopForm
Note that sfMyForm is skipped and the parent jumps straight from MyForm to TopForm.

In other words, TypeOf Ctl.Parent Is SubForm will never return True, regardless of which control the Ctl variable represents:

?TypeOf Form_TopForm.sfMyForm.Controls(0).Parent Is SubForm
False

Read that above line of code carefully.  What it's saying in plain English is this, "the parent of subform sfMyForm's first child is not a subform."  If I didn't know any better, I'd think that sfMyForm was having an existential crisis.

What's the alternative?

In the GetFormByCtl() function, we use the TypeOf...Is expression to test each control on our way up the control ancestry to see if it is a form object.

If TypeOf Ctl.Parent Is Form Then

As I demonstrated in my article on the TypeOf...Is expression, a single variable can be a type of multiple compatible types.  For example, a text box control is a type of Access.TextBox, Access.Control, and Object.  

Unfortunately, we can't simply check to see if the Ctl.Parent is a type of subform because that check will never return True.  

The simplest workaround is to check to see if a parent control that is a form has any parents of its own.  If the form object does have a parent, then we know the object is actually a subform.  If the object does not have a parent, ...well..., then Access will raise an error.  And since there is no way to avoid the error in this case, our best option is to create a simple function to isolate this error from the rest of our code: IsSubform().

'Returns True if the passed form object is a subform of a parent form
Function IsSubform(Frm As Form) As Boolean
    On Error Resume Next
    IsSubform = (Not Frm.Parent Is Nothing)
End Function

GetFormObjectByCtl(): a private function

We'll start by modifying the existing GetFormByCtl() function and converting it into a private function.  This modified function takes a boolean parameter to indicate whether the form object should be returning the top form object or just the first one it encounters:

Private Function GetFormObjectByCtl(Ctl As Object, _
                                    ReturnTopForm As Boolean) As Form
    If TypeOf Ctl.Parent Is Form Then
        If ReturnTopForm Then
        
            If IsSubform(Ctl.Parent) Then
                'Recursively call the function if this is a subform
                '   and we need the top form
                Set GetFormObjectByCtl = GetFormObjectByCtl( _
                                             Ctl.Parent, ReturnTopForm)
                Exit Function
            End If
        End If
        Set GetFormObjectByCtl = Ctl.Parent
    Else
        'Recursively call the function until we reach the form
        Set GetFormObjectByCtl = GetFormObjectByCtl( _
                                     Ctl.Parent, ReturnTopForm)
    End If
End Function

GetFormByCtl(): modified version

Here's the modified version of the GetFormByCtl() function that depends on the above private function:

'Returns the first form parent of the given control
Function GetFormByCtl(Ctl As Control) As Form
    Set GetFormByCtl = GetFormObjectByCtl(Ctl, False)
End Function

The Code: GetTopFormByCtl()

And here is the new code that returns the top (outermost) form based on any given control object:

'Returns the top/outermost form for the given control
Function GetTopFormByCtl(Ctl As Control) As Form
    Set GetTopFormByCtl = GetFormObjectByCtl(Ctl, True)
End Function

Sample usage

Here's a quick test function to verify the functions work as expected:

Sub GetFormObjects()
    Dim Lbl As Label
    Set Lbl = Form_TopForm.sfMyForm.Form.MyLabel
    
    Debug.Print Lbl.Name
    Debug.Print GetFormByCtl(Lbl).Name
    Debug.Print GetTopFormByCtl(Lbl).Name
End Sub

A Final Note on Three Functions Instead of One

I wanted a way to return:

  • the closest form to a given control
  • the top form for a given control

I could have easily implemented these two features with a single function.  In fact, that's exactly what I did with the GetFormObjectByCtl() function.  

So, why did I bother making the GetFormObjectByCtl() function private when I could have simply made it public and been done?

Honestly, it's simply a matter of personal preference.

However, I chose to split it into three functions for a couple of reasons:

  • Backward compatibility: using 3 functions allowed me to continue using the GetFormByCtl() function as I was without having to change any code.  Yes, I realize I could have achieved the same thing by adding a default value of False to the ReturnTopForm boolean variable.  But that brings me to my next point...
  • Readability: calling appropriately named functions leads to more readable code than calling functions with unnamed boolean arguments:
'Isn't this...
    Debug.Print GetFormByCtl(Lbl).Name
    Debug.Print GetTopFormByCtl(Lbl).Name

'...better than this...
    Debug.Print GetFormObjectByCtl(Lbl, False).Name
    Debug.Print GetFormObjectByCtl(Lbl, True).Name

All original code samples by Mike Wolfe are licensed under CC BY 4.0