Oct 25 2022 06:11 PM - edited Oct 25 2022 06:13 PM
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
thank you very much.
Let me know if any questions.
Oct 25 2022 10:51 PM - edited Oct 25 2022 10:52 PM
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
Oct 26 2022 04:18 AM
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....
Oct 26 2022 04:22 AM
Oct 26 2022 07:19 AM - edited Oct 26 2022 07:21 AM
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
Oct 26 2022 09:37 AM
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:
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:
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!
Oct 26 2022 10:12 AM - edited Oct 26 2022 10:13 AM
Solutionhere 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.
Oct 26 2022 01:41 PM
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!
Oct 26 2022 10:12 AM - edited Oct 26 2022 10:13 AM
Solutionhere 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.