Search code examples
vbams-access

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)

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.

Solution

  • 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.