Solved OpenArgs From SubF.CurrentRecord[FieldName] (2 Viewers)

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

 

[Pat Hartman]

As I said June, the form issue is relatively new. It also is not immediate. I'm not sure when Access changes the RecordSource maybe it doesn't happen until you do a C&R. The form always works when you build it. It breaks sometime later. I didn't even try to track down "when". When the problem occurred, I just shook my fist at the MS people who make stupid changes like this and added bound controls to fix the problem. They are tiny and hidden so the look of the form didn't change. Since compiled code doesn't just break, old apps don't fail. I think it is a subsequent change to the form that triggers the action and then the code fails.

I discovered this because I have LastChangeDT and ChangeBy in most tables. These fields are rarely visible to the user since they are for my internal use only. They help to track down who is making changes and when so they never need to be bound to controls. They are only updated in the form's BeforeUpdate event as the changes are being saved.

 

[MajP]

As I said June, the form issue is relatively new. It also is not immediate. I'm not sure when Access changes the RecordSource maybe it doesn't happen until you do a C&R. The form always works when you build it. It breaks sometime later. I didn't even try to track down "when". When the problem occurred, I just shook my fist at the MS people who make stupid changes like this and added bound controls to fix the problem. They are tiny and hidden so the look of the form didn't change. Since compiled code doesn't just break, old apps don't fail. I think it is a subsequent change to the form that triggers the action and then the code fails.

So this might be problematic for me because what I have been doing is adding the control temporarily to save the form. Then opening the form and getting rid of it. I just feel it is cleaner not having a hidden control for the purpose of referencing the field. I am wondering now if the user then modifies the form at a later date that the code could fail. I guess I should be leaving those hidden controls.

 

[Pat Hartman]

Thanks Maj. At least I'm not imagining the error. I can't say when the change occurred since I am mostly retired and although I use Access a lot, it isn't in a production environment. At least a year ago but it could be two by now.

I don't like having hidden fields either. But, we do what we have to do to justify the stupid "change for the sake of change" changes the children in Redmond make. I make their background bright yellow so they stand out and no one has to think about them once they know they are deliberate and hidden.

 

[MajP]

I don't like having hidden fields either. But, we do what we have to do to justify the stupid "change for the sake of change" changes the children in Redmond make. I make their background bright yellow so they stand out and no one has to think about them once they know they are deliberate and hidden.

If I remember correctly at one time you could not reference a field that was not bound. Then for years you could. Now you cannot again. I guess they like teasing us.

 

[Edgar_]

Maybe the referencing is done differently in those cases. If you have any sample, I could check it out for A2016 and A2021.

 

[Mike Krailo]

This thread is very disturbing if true. Especially the part about unbound control on a form cannot be referenced. I just tested this and didn't have a problem. What are the test conditions to confirm this?

 

[June7]

Not saying CONTROL cannot be referenced, saying a RecordSource FIELD that is not the ControlSource of any control might not be able to be referenced in a ControlSource expression. I seem to remember having this issue on reports back with 2003 or 2007 but never on form. Now I am using 2021 and no issue with form or report.