Syntax To Refer To Subform's Specific Source Object

[tammiep]

Hi,

I have a form frmTherapyDetails that contains a subform placeholder called SbFrmSubForm. This subform's source object changes depending on the user's selection from a combobox on the main form. Having one of these subform's open is a requirement in order for another form to be opened when a button on this subform is clicked. So, in other words, the sourceobject for one subform is "Tracheostomy/Stoma". On this subform is a button that when clicked opens another form called frmCorkingDetails. But in order for frmCorkingDetails to open, the subform Tracheostomy/Stoma must be open. When I use the following code on frmCorkingDetails Form_Open, it's not recognizing that the subform is open:

Private Sub Form_Open(Cancel As Integer)

If Not IsLoaded("Tracheostomy/Stoma") Then
MsgBox "Open the Corking Details form using the Corking Details button on the Tracheostomy/Stoma form."
Cancel = True
End If

End Sub

I know that the subform is not recognized as a form in the forms collection, and I have tried various changes in the syntax to refer to this particular form when displayed as a subform on the main form. I've tried:

If Not IsLoaded(forms![frmTherapyDetails]![SbFrmSubForm].SourceObject = "Tracheostomy/Stoma") Then
etc....

Doesn't work. I've tried:

If Not IsLoaded(forms![frmTherapyDetails]![SbFrmSubForm]![Tracheostomy/Stoma].SourceObject) Then
etc...

No go. I've also tried:

If Not IsLoaded(forms![frmTherapyDetails]![Tracheostomy/Stoma].SourceObject) Then

and this:

Dim strTrachSbFrm As String
strTrachSbFrm = [Forms]![frmTherapyDetails]![SbFrmSubForm].SourceObject = "Tracheostomy/Stoma"

If Not IsLoaded(strTrachSbFrm) Then
MsgBox "Open the Corking Details form using the Corking Details button on the Tracheostomy/Stoma form."
Cancel = True
End If

Still not working. I just can't figure out the proper syntax to refer to this specific subform. Any ideas?

Thanks

 

[MajP]

A generic function that can be used anywhere.

Public Function isSubFormLoaded(subFrmName As String) As Boolean
  'pass the subform name
  Dim subFrm As Access.Form
  Dim frm As Access.Form
  Dim ctrl As Access.Control
  For Each frm In Forms
    For Each ctrl In frm.Controls
      If ctrl.ControlType = acSubform Then
        If ctrl.Form.name = subFrmName Then
           isSubFormLoaded = True
           Exit Function
        End If
      End If
    Next ctrl
  Next frm
End Function

if not isSubFormLoaded("Tracheostomy/Stoma") Then ...

 

[MajP]

here is an update to handle on source object.

Public Function isSubFormLoaded(subFrmName As String) As Boolean
  Dim subFrm As Access.Form
  Dim frm As Access.Form
  Dim ctrl As Access.Control
  For Each frm In Forms
    For Each ctrl In frm.Controls
      If ctrl.ControlType = acSubform Then
        'Need this for no source object
        If Not ctrl.SourceObject = "" Then
           If ctrl.Form.name = subFrmName Then
              isSubFormLoaded = True
              Exit Function
           End If
        End If
      End If
    Next ctrl
  Next frm
End Function

 

[MajP]

If you know the name of the subform control and the name of the parent form a more direct is.

Public Function isSubFormLoaded2(parentForm As String, subFormControl As String, subFormName As String) As Boolean
  If CurrentProject.AllForms(parentForm).IsLoaded Then
     isSubFormLoaded2 = Forms(parentForm).Controls(subFormControl).SourceObject = subFormName
  End If
End Function

 

[tammiep]

Thank-you MajP for your suggestions. Much appreciated.

 

[MajP]

Here is one more fix on a generic function. No need to return the form object of the subform, just check the sourceobject.

Public Function isSubFormLoaded(subFrmName As String) As Boolean
  Dim subFrm As Access.Form
  Dim frm As Access.Form
  Dim ctrl As Access.Control
  For Each frm In Forms
    For Each ctrl In frm.Controls
      If ctrl.ControlType = acSubform Then
        If ctrl.SourceObject = subFrmName Then
           isSubFormLoaded = True
           Exit Function
        End If
      End If
    Next ctrl
  Next frm
End Function