Access VBA - How to get the properties of a parent subform, or, get the user-given name for a subform (not the object reference name)

Viewed 546

In MS Access 2016, let's say I have 2 forms: frmMain and frmBaby.

I have embedded frmBaby as a subform on frmMain. I have embedded on frmBaby a control (let's say it's a textbox, but it could be any control) named tbxInput.

On frmMain, since frmBaby is a "control" on frmMain, I have given that control the traditional name of subfrmBaby.

Now, in VBA, an event on subfrmBaby passes the tbxInput control ByRef (as Me.tbxInput) to a function that is meant to return the .Left property of the parent of the control passed ByRef. That is, I need the function to determine the .Left property for the location of subfrmBaby on frmMain. (The function is more complicated than this, but for the sake of keeping this question let's just say the function is returning the .Left property value because the .Left value is what I need to perform the function.)

Let's say the function is: Public Function fncLocation(ByRef whtControl As Variant) as Long

(I use Variant so that null values can be passed.)

Here is the code that I expected to return the .Left value of the parent (i.e., subfrmBaby) of whtControl: lngLeft = whtControl.Parent.Left

However, that gives me an error of: "Application or object-defined error"

When I use the immediate window to check things out I find that whtControl.Parent.Name is "frmBaby" and not "subfrmBaby" which makes it problematic to reference the subform on frmMain since I cannot figure out how to get the actual name given to the control on frmMain from the object passed to the function and so I cannot reference the subform by name either.

Questions:

  1. How can I get the .Left value for the parent of the control passed to this function?

  2. How can I get the actual name assigned to the subform control on frmMain? In this case, I need the name of "subfrmBaby" rather than "frmBaby."

Thanks in advance for ideas.

3 Answers

You can do this by iterating the controls on the main form, assuming whtControl is the form object of the subform (if it's a textbox, it's whtControl.Parent.Parent and If c.Form Is whtControl.Parent Then)

Dim mainForm As Form
Set mainForm = whtControl.Parent
Dim c As Access.Control
Dim subformControl As Access.Control
For Each c In mainForm.Controls
    If TypeOf c Is SubForm Then
        If c.Form Is whtControl Then
            Set subformControl = c
            Exit For
        End If
    End If
Next
If Not subformControl Is Nothing Then
    Debug.Print subformControl.Left
End If

Note that iterating controls comes at a performance penalty, but this code should still take milliseconds, not seconds. Also, since we test reference equality, it works even if the same subform is present multiple times on the parent form.

I just had this issue, and I think I solved it! Thanks to Eric A's answer above to get me started. I tweaked it and built on it for my use. In my case, I needed to save the "full" address of a control to build and facilitate a control log (used to log both user actions for auditing and to allow for users to "undo" an action). I have several duplicated subforms in several sub-form controls, and a few sub-sub forms (each displaying differently filtered and sorted data), so I couldn't rely on simply knowing the subform's name, I also needed the subform control name. This also leverages others' work (as noted in the code notes with some tweaks to allow easier re-use for us. I've posted it here, hopefully it will help someone else. I know I've used SO a lot.

How we use it:

On a form, after logging an action, we record the control's ID info, which calls a function to get the toppost form (this is used in conjunction with afterUpdate event so we refresh the main form and subform). We also use the HWND to validate some other items elsewhere, and to grab a form if we don't have the initial form reference. If you use this and modify it, please point back to here and give comments.

Specific Function Code to get Control "address" and get control from address

' Posted on StackOverflow 2022 February 18 in response to Question:
' https://stackoverflow.com/q/66425195/16107370
' Link to specific answer: https://stackoverflow.com/a/71176443/16107370
' Use is granted for reuse, modification, and sharing with others 
' so long as reference to the original source is maintained and you 
' help lift others up as others have done those who helped with this concept 
' and code.

Private Function GetControlAddress(ByRef ControlTarget As Object, _
                                ByRef ParentForm As Access.Form) As String
' Used in concert with building a form ID, this allows reflection back to the specific
' subform control and containing subform.
    Dim ControlSeek As Access.Control
    
    If TypeOf ControlTarget Is Form Then
    ' You need to dig through the whole list to get the specific controls for proper reflection down.
        For Each ControlSeek In ParentForm.Controls
            If ControlSeek Is ControlTarget Then
                GetControlAddress = ParentForm.Name & FormIDHWNDSep & ParentForm.Hwnd & FormIDHWNDSep & ControlTarget.Name & FormIDFormSep
                Exit For
            ElseIf TypeOf ControlSeek Is SubForm Then
                If ControlSeek.Form Is ControlTarget Then
                    GetControlAddress = ParentForm.Name & FormIDHWNDSep & ParentForm.Hwnd & FormIDHWNDSep & ControlSeek.Name & FormIDFormSep
                End If
            End If
        Next ControlSeek
    Else
    ' If you're not looking for a form, then you can skip the slow step of running through all controls.
        GetControlAddress = ParentForm.Name & FormIDHWNDSep & ParentForm.Hwnd & FormIDHWNDSep & ControlTarget.Name & FormIDFormSep
    End If

End Function

Public Function GetControlByAddress(ByRef StartingForm As Access.Form, ByRef strControlAddress As String) As Access.Control
' Given a control address and a starting form, this will return that control's form.
    Dim ControlTarget As Access.Control
    Dim TargetForm As Access.Form ' This is a reference to the hosting control
    'Dim ControlSeek As
    Dim FormIDArr() As String
    Dim FormInfo() As String
    Dim ControlDepth As Long
    Dim CurrentDepth As Long
    
    If strControlAddress = vbNullString Then GoTo Exit_Here
    
    FormIDArr = Split(strControlAddress, FormIDFormSep)
    ' Because there's always a trailing closing mark (easier to handle buidling address), we skip the last array
    ' value, as it's always (or supposed to be...) empty.
    ControlDepth = UBound(FormIDArr) - LBound(FormIDArr)

    ' Split out the form's Specific Information to use the details.
    FormInfo = Split(FormIDArr(CurrentDepth), FormIDHWNDSep)
    
    ' The specific control is located in the 3rd element, zero referenced, so 2.
    Set ControlTarget = StartingForm.Controls(FormInfo(2))
    
    ' If ControlDepth is 1 (control is on passed form) you can skip the hard and slow work of digging.
    If ControlDepth > 1 Then
        For CurrentDepth = 1 To ControlDepth - 1
        ' Note: you start at 1 because you already did the first one above.
            ' Split out the form's Specific Information to use the details.
            
            FormInfo = Split(FormIDArr(CurrentDepth), FormIDHWNDSep)
            Set TargetForm = ControlTarget.Form
            Set ControlTarget = TargetForm.Controls(FormInfo(2))
        Next CurrentDepth
    End If
    
Exit_Here:
    Set GetControlByAddress = ControlTarget
End Function

Required Helper Functions

Note, I use a property for the separators as there is some user locale handling (no included), and it also ensures that if we do change the separator it remains consistent. In this example, I simply set them to a character which is unlikely to be used in a form name. You will need to ensure your forms don't use the separator characters.

Public Function hasParent(ByRef p_form As Form) As Boolean
' Borrowed concept from https://nolongerset.com/get-top-form-by-control/
' and modified for our uses.
    On Error Resume Next
    hasParent = (Not p_form.Parent Is Nothing)
    Err.Clear ' The last line of this will cause an error. Clear it so it goes away.
End Function

Private Function GetFormObjectByCtl(ByRef ctl As Object, _
                                    ByRef ReturnTopForm As Boolean, Optional ByRef strControlAddress As String) As Form
    
    strControlAddress = GetControlAddress(ctl, ctl.Parent) & strControlAddress
    
    If TypeOf ctl.Parent Is Form Then
        If ReturnTopForm Then
        
            If hasParent(ctl.Parent) Then
                'Recursively call the function if this is a subform
                '   and we need the top form
                
                Set GetFormObjectByCtl = GetFormObjectByCtl( _
                                             ctl.Parent, ReturnTopForm, strControlAddress)
                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, strControlAddress)
    End If
End Function

Public Function GetFormByCtl(ctl As Object, Optional ByRef strControlAddress As String) As Form
    Set GetFormByCtl = GetFormObjectByCtl(ctl, False, strControlAddress)
End Function

Public Function GetTopFormByCtl(ctl As Object, Optional ByRef strControlAddress As String) As Form
    Set GetTopFormByCtl = GetFormObjectByCtl(ctl, True, strControlAddress)
End Function


Public Property Get FormIDHWNDSep() As String
    FormIDHWNDSep = "|"
End Property
Public Property Get FormIDFormSep() As String
    FormIDFormSep = ";"
End Property

Interesting. I don't think you can.

As you have seen, the parent of whtControl is its form, frmBaby.
The parent of that one is frmMain. The subform control is not part of the object chain when "going up", only when going down.

If you always use the naming scheme as in the question, you could do something like this (air code):

strSubform = whtControl.Parent.Name
strSubformCtrl = "sub" & strSubform
Set ctlSubform = whtControl.Parent.Parent(strSubformCtrl)
Related