Solved OpenArgs From SubF.CurrentRecord[FieldName]

[dalski]

Activated by the SubF btn_Click event I am trying to open a childForm (not pictured) to a specific record using OpenArgs, from a field of the recordsource (not a control but a field which is not shown in the controls of the form). The form is based on a query (unsure if this makes a difference. Tried creating a demo file but was taking ages so hoping someone gets what I mean . I'm pretty weak so bear with.

The btn_Onclick event fires in the subF, I need to get a value for the currentRecord [fieldName_FK] in the SubF of the where the cursor is in the recordset; so I thought:

Private Sub btnInSubF_Click()
    Me.Parent.Form.CurrentRecord [FieldName_FK]
    DoCmd.OpenForm "ChildFName", , , , , , Me.Parent.Form.Recordset.CurrentRecord[FieldName_FK]
End Sub

Nope. Was trying to assign the openArg as an object variable also, & trying to assign a long variable type to the but no joy either. Been on it all day trying every permutation possible in my mind.

 

[Edgar_]

Try this

Private Sub btnInSubF_Click()
    DoCmd.OpenForm "ChildFName", , , "FieldName_FK=" & Me.Parent.FieldName_FK
End Sub

Make sure the FieldName_FK field exists in the parent form.

 

[Uncle Gizmo]

Try:-

Private Sub btnInSubF_Click()
    Dim lngForeignKey As Long
    lngForeignKey = Me![FieldName_FK] ' Retrieve value from current record

    ' Open the child form and pass the value via OpenArgs
    DoCmd.OpenForm "ChildFName", , , , , , lngForeignKey
End Sub

 

[dalski]

Thanks so much @Edgar_ & @Uncle Gizmo .

 

[Pat Hartman]

No one mentioned the change to how forms work that happened sometime in the past year or so so I can't tell what actually fixed your problem so I will mention it specifically and you can clarify if that is what you did. In old versions of Access, Access used to rewrite a report's RecordSource to eliminate any column not actually bound to a control. That meant that if you referenced a field that was in your original query but not bound to a control, the code would work when you tested it but fail the next time you opened the database because the field would no longer be available. Forms did not rewrite your RecordSource so this was never an issue. Sometime in the not too distant past but I can't say exactly when the change happened, the form was changed to behave the way reports worked. So, that would explain the behavior. The alternative answer is that you were not actually referencing the control correctly.

 

[dalski]

It's tempting to hide behind the more technical issue & save face. But I have to be honest - I was referencing the field incorrectly of the record source. Got very confused with sub form referencing & I don't even think it was needed. I was not referencing a control on the form as I'm trying not to bloat out the form with unnecessary controls.

Thanks for sharing Access history, I am shocked things have gone backwards - I am lost for words.

 

[June7]

I just tested with Access 2021 (MS Office Pro Plus). Form still opens the "old way" - referencing field not bound to control as criteria works. I tested with RecordSource set as table and SQL. Then I tested same with a report - also works. Even after closing/reopening db - RecordSource is not altered.

Seems I used to (back with 2003/2007/2010) encounter issue on report with referencing field in a textbox expression if field was not ControlSource of another textbox.