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)?
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
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
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.
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
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
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