Find open subform

Question

I have a subform with a button that opens another form.
On the secondary form, the user can select an address.
The selected address should be applied to the calling form.

I pass the window handle when opening the child form.
But when it tries to find the calling form in the Forms collection, it isn't there.
I suspect that is because the calling form is actually a subform.
I don't know where to go from here.

Calling the form, passing the windows handle

    OpenCCCustAddr [CustFID], "CCInt", Me.hWnd

In the Form Close event, I try to set the address values on the calling form, but GetFormByHWND returns null.

Set frm = GetFormByHWND(Me!txtCallingHWND)
          // Me!txtCallingHWND here is populated and looks reasonable
    frm!BillStreet = strAddr  // This blows up since frm is null
    frm!HolderZipCode = strZip
    frm!AddressUpdated = -1
Set frm = Nothing


Public Function GetFormByHWND(lngHWND As Long) As Form
   Dim frm As Form
   Dim nm As String

   Select Case lngHWND
        Case 0
        Case Else
            For Each frm In Forms
            nm = frm.NAME   // the name of the parent form shows, but not my calling subform
               If frm.hWnd = lngHWND Then
                  Set GetFormByHWND = frm
                  Exit For
               End If
            Next
   End Select
End Function

For Each and For I=0 to Count-1 both give the same results. The form just isn't in Forms. It's possible that it is because it is a subform.

I tried searching the subforms, but this blows up when I check ctl.hWnd with "Object doesn't support this property"

Public Function GetFormByHWND(lngHWND As Long) As Form
   Dim frm As Form
   Dim ctl As Access.Control

   Dim nm As String
   
   Select Case lngHWND
        Case 0
        Case Else
            For Each frm In Forms
nm = frm.NAME
               If frm.hWnd = lngHWND Then
                  Set GetFormByHWND = frm
                  Exit For
               End If
            Next

            Rem If we didn't find the form, check for a subform
            If GetFormByHWND Is Nothing Then
                For Each frm In Forms
nm = frm.NAME
                    For Each ctl In frm.Controls
                        If ctl.Properties("ControlType") = acSubform Then
nm = ctl.NAME
                            If ctl.hWnd = lngHWND Then  // Error: "Object doesn't support this property" 
                                Set GetFormByHWND = ctl
                                Exit For
                            End If
                        End If
                    Next
                Next
            End If
   End Select
End Function

Correct, a subform is not in Forms collection. Reference subform through subform container control. Exact syntax depends on what needs to be done with subform. To set value of a control, like: Forms!mainformName.subformcontainerName.Form.BillStreet. I have never used Windows handles to reference forms. I just pass name of form. Unless you are opening multiple instances of same form, I don't see need for Windows handles. Why would you need to set values in subform with data from a popup? — June7
@June7 Your comment led to the answer. But I don't see any way to give you proper credit. — BWhite
I would have to create an answer for you to upvote and/or accept. Glad to have helped. — June7

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2020-12-05T00:52:24

First, it not at all clear why all that code and hwn stuff is required?

We assume that you have a form.

On that form, you have a button, and it launches the 2nd form.

so, in first form, we have this:

  ' write data to table before launching form
  If Me.Dirty = True Then Me.Dirty = False
  
  DoCmd.OpenForm "formB"

Ok, now in formB on-load event, we have this:

  Option Compare Database
  Option Explicit
  
  Dim frmPrevious      As Form
  Dim frmPreviousSub   As Form
  
  Private Sub Form_Load()
  
     Set frmPrevious = Screen.ActiveForm
     
     Set frmPreviousSub = frmPrevious.MySubFormControl.Form
     
     
     ' do whatever
     
  End Sub

So now we have both a reference to the previous form, and also the sub form.

Say the user selects some address and hits the ok button.

The code then does this:

frmPreviousSub!AddressID = me!ID    ' get/set the PK address ID
docmd.Close acForm, me.name

So no need for all that world poverty, grabbing and looping hwnd or any such hand stands.

Just a few nice clean lines of code.

Now, I DO HAVE a recursive loop that will return the form handle ALWAYS as a object reference, and you thus don't even have to hard code the form(s) name.

So, say that main form had 2 sub forms, and on those to sub forms, you have

A Company address, and a ship to address. So, you want to launch form B from EITHER of these two sub forms, and when you select a address, you return that value, and thus two or even potential 3 sub forms could in fact call this way call pop up address selector form.

The way you do this is similar to the above code, but we do NOT need to hard-code the sub form.

The code will now look like this:

  Private Sub Form_Load()
        
           Dim f       As Form
           Set f = Screen.ActiveForm     ' pick this up RIGHT away - 
                                         ' previous active form only valid
                                         ' in open/load event
           
           ' we have the previous active form, get the sub form.
           
           Set frmPrevous = GetSubForm(f)
    
   End Sub

Note that the sub form can be 3 or even 5 levels deep. This routine is "recursive". It grabs the previous form, then checks if a sub form has focus. If the sub form has focus, then it gets that control, and if that control is a sub form, then it just keeps on going until such time we drill down this rabbit hole and NO MORE drilling down can occur.

This routine should be placed outside of the form and placed in your standard "global" module of routines.

  Public Function GetSubForm(f As Form) As Form
  
     Static fs         As Form
     
     If f.ActiveControl.Controltype = acSubform Then
        GetSubForm f.ActiveControl.Form
     Else
        Set fs = f
     End If
  
     Set GetSubForm = fs
  
  End Function

So note how if it find that a sub form has the focus? Well then it just calls itself again with that form and keeps on drilling down. As a result, it don't matter if the form is 1 or 5 levels deep. The resulting "frmPrevous" will be a valid reference to that sub form, and thus after you select or do something in the supposed popup form? You can set the value of some PK or whatever and then close the form.

There is no hwnd, very clean code, and the recursion trick means that even for nested sub forms more then one deep, your frmPrevious is in fact a reference to the form that launch the form we pop up for the user to select whatever.

If you don't have a common Address ID column? Then our popup form should ASSUME that you always have a public variable defined in the calling form.

Say ReturnAddressID as long

Make sure you dim the value as public, say like this:

Public ReturnAddressID as long

So, now in our popup form, we can do this:

frmPrevious.ReturnAddressID = me!PD
frmPrevous.MyUpdate

(we assume that all forms that call the popup also have a public function names MyUpdate.

Thus, now we have a generalized approach, and 2 or 10 different address forms, even as sub forms can now call the one address picker. As long as any of those forms adopts that public ReturnAddressID and a public function MyUpdate, then we can pass back the values, and MyUpdate will shove/take/set the ReturnAddressID into whatever column and value you use for the Address ID in that sub form.

And of course if there are no sub forms, the routine will just return the top most form that called the pop up form.

Find open subform

2 Answers