Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Oct 25, 2022
Solved

Open Subform to ID that's on an Unbound Main Form

Experts,    I have an unbound form: frmFXTracker On this unbound form I have a subform: frmFXParent   What I need to do is open this form FXTracker where the frmFXParent.txtIDFXParent = Me.txtID...
access
  • arnel_gp's avatar
    arnel_gp
    Oct 26, 2022

    Tony2021 

    here is a demo.

    open report Table1 (report view).

    then open frmFXTracker

     

    on the report click the amount textbox.

    did it go to the record on the subform?

     

    view the code on the textbox on report and also the code on the subform.

arnel_gp's avatar
arnel_gp
Steel Contributor
Oct 25, 2022

pass your filter as OpenArgs parameter (not WhereClause), example:

Private Sub txtAmountORIG_Click()
DoCmd.OpenForm FormName := "frmFXTracker", OpenArgs := "txtIDFXParent=" & Me.txtIDFXParent
End Sub

Now open "frmFXTracker" form in desgin view and click on the subform.
Add Code on it's Load Event, and add this code:

Private Sub Form_Load()
Dim varFilter As Variant
varFilter = Me.Parent.OpenArgs
If IsNull(varFilter) = False Then
Me.Filter = varFilter
Me.FilterOn = True
End If
End Sub

  • Tony2021's avatar
    Tony2021
    Steel Contributor
    Oct 26, 2022

    arnel_gp 

    Hi Arnel, thank you very much for the response.

     

    Question:
    What if frmFXTracker is already open?   this is the case. 
    I am thinking I need to put the ON Load event in a different event?
    I tried On Activate but get an error (below). I dont think it would be ON Open either since its already open. Not sure about ON Current either since the form is already open.

    Let me know what you think. Maybe close the frmFXTracker and reopen? I imagine you have a better idea.   Let me know what you suggest.  

     

    frmFXTracker ON Activate: 

     


    thank you....

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Oct 26, 2022
      I do have a function to check if the form IsLoaded though:

      Function IsLoaded(ByVal strFormName As String) As Integer
      ' Returns True if the specified form is open in Form view or Datasheet view.

      Const conObjStateClosed = 0
      Const conDesignView = 0

      If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
      If Forms(strFormName).CurrentView <> conDesignView Then
      IsLoaded = True
      End If
      End If

      End Function
      • arnel_gp's avatar
        arnel_gp
        Steel Contributor
        Oct 26, 2022

        so form frmFXTracker is Open and on some Report you click txtAmountORIG textbox to filter the sub-form of frmFXTracker?

        remove the code i gave you, instead add a Public Sub on the subform:

        Public Sub subApplyFilter(Byval id As Long)
        Wtih Me
            .Filter = "txtIDFXParent=" & id
            .FilterOn = True
        End With
        End Sub

        back to your Report's textbox click event, modify this to:

        Private Sub txtAmountORIG_Click()

        If IsLoaded("frmFXTracker") Then
              [Forms]![frmFXTracker]!frmFXParent.Form.subApplyFilter Me.txtIDFXParent

        End If

        End Sub