Reference Current subform control dynamically

[Friday]

I have a form - frmBridge1. It has a subform sqlBridge1_subform. In the first field on the subform, I am using the gotfocus event to do some things. I want to use 4 different subforms on this main form, but the function I originally built was for a single subform. I want to reference the subform dynamically and build a pointer to the current control name.

Here is part of the code:

Dim Qtext As String
Dim rstSubForm As Form
Dim rstMainForm As Form


Set rstSubForm = Screen.ActiveControl.Parent 'this gives me the current subform name
Set rstMainForm = Screen.ActiveForm 'this returns the main form name
'then I would have a line like:
Qtext = Forms!rstMainForm.rstSubForm.Form.ActiveControl.Name 'this errors out - "Microsoft Office Access can't find the form 'rstMainForm' referred to in a macro expression or Visual Basic code"


'Currently I am using this code, which works great for the existing subform:
Qtext = Forms!frmBridge1.sqlBridge1_subform.Form.ActiveControl.Name

I am sure this is clear as mud. Can anyone point me in the right direction?

 

[Banana]

Try this.

Forms(rstMainForm).Controls(rstSubform.Name).Form.ActiveControl.Name

 

[LPurvis]

Can you explain a bit about what you're doing overall?
Do you want a reference to the active control on the subform?
Which would, obviously, be just Screen.ActiveControl
i.e.
Qtext = Screen.ActiveControl.Name
There's no need to build up a full expression then - you already have an object pointing to it.
Or are you using the expression in some non-VBAcapacity?

If you need a fairly robust way of determining the name of the containing subform control...

Function fGetCurrentSubformName(Optional frmSub As Form) As String
'Returns the name of the containing subform control
On Error Resume Next
    Dim ctl As Access.Control
    Dim strDummy As String
    
    If frmSub Is Nothing Then
        'No form passed, assume a control in the subform has focus
        Set frmSub = Screen.ActiveControl.Parent
    End If
    
    strDummy = frmSub.Parent.Name
    If Err <> 2452 Then
        'Form has parent
        For Each ctl In frmSub.Parent.Controls
            If ctl.ControlType = acSubform Then
                If ObjPtr(ctl.Form) = ObjPtr(frmSub) Then
                    If Err = 0 Then
                        Debug.Print ctl.Name
                        fGetCurrentSubformName = ctl.Name
                    End If
                End If
            End If
        Next
    End If
    
End Function

Which can be called from the subform e.g.
MsgBox fGetCurrentSubformName(Me)
or just
MsgBox fGetCurrentSubformName
if you want to allow the default to be assumed.
The above could easily be adapted to provide a full syntax.

 

[Friday]

Can you explain a bit about what you're doing overall?
Do you want a reference to the active control on the subform?
Which would, obviously, be just Screen.ActiveControl
i.e.
Qtext = Screen.ActiveControl.Name

How in the hell did I miss that? Thanks much. I am passing the field name (control name) to a parameter query so that I can display a matching message (question) in a text box in the main form.

 

[LPurvis]

No probs at all.

(And I love your sig quote btw - I feel as if it's almost speaking directly to me at times lol).

 

[Friday]

(And I love your sig quote btw - I feel as if it's almost speaking directly to me at times lol).

It was my late brother's favourite saying. I've clung to it since we lost him three years ago.