SOLVED

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

Steel Contributor

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.txtIDFXParent (this txtIDFXParent is located on another form).  

 

I have the below code on a report (txtAmountORIG_Click) but I get a 2491 runtime error.  I have been trying to piece together code I found online and I know the code below is not correct and I think its the red text.  

 

this is on a report: 

Private Sub txtAmountORIG_Click()

       DoCmd.OpenForm "frmFXTracker", , , "Forms!  [frmFXTracker].frmFXParent.Form.txtIDFXParent=" & Me.txtIDFXParent

End Sub

Tony2021_0-1666745906604.png

 

thank you very much.

Let me know if any questions. 

 

 

7 Replies

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

@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: 

Tony2021_0-1666783006926.png

 


thank you....

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

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

@arnel_gp 

 

Arnel,

to answer your question, yes. The frmFXTracker is open and I will sometimes click txtAmountORIG_Click() from another report and go to frmFXTracker.

 

I have added and removed the codes as instructed.
It seems I have an msg box pop up when I click on txtAmountORIG_Click() in my rpt:

Tony2021_0-1666801315273.png

 

txtIDFXParent I can guarantee is the field name of IDFXParent on frmFXParent (the subform of frmFXTracker unbound Parent form).  

 

If I click cancel then the debugger highlights the code in my report

Tony2021_1-1666802081393.png

 

 

I am not sure if a filter is what I need since I also noticed an error on frmFXTracker if I open it up on its own instead of opening frmFXTracker from the click event of txtAmountORIG_Click().  

 

I only need the record frmFXParent.txtIDFXParent to be selected (ie the record selector that you see in datasheet).  I am thinking there would have to be more codes if using a filter to account for opening the form on its own but I dont really know...just assuming.  

 

Let me know if you have any questions.  Thank you for the help!

 

best response confirmed by Tony2021 (Steel Contributor)
Solution

@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 

I see what the issue is.
the actual name of the field is in my table is IDFXParent and the code had it as txtIDFXParent. I see in your table that the actual name is also txtIDFXParent...so I changed it to the below (removed the "txt" part)

 

Public Sub subGoToID(ByVal ID As Long)
With Me.RecordsetClone
.FindFirst "IDFXParent = " & ID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
I also needed to modify the below for when the frmFXTracker is not open. Most of the time it is though.
Private Sub AmountORIG_Click()
If IsLoaded("frmFXTracker") Then
[Forms]![frmFXTRacker]![frmFXParent].[Form].subGoToID Me.txtIDFXParent
[Forms]![frmFXTRacker].SetFocus
Else
DoCmd.OpenForm "frmFXTRacker"
[Forms]![frmFXTRacker]![frmFXParent].[Form].subGoToID Me.txtIDFXParent

End If

Its working great!  thank you again.  Really appreciate the expert help!

1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

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

View solution in original post