Populate fields in main form from record in subform

Question

In Access 2016, I have a main form, ClientInfoForm, which contains a subform, EditTransactions_subform. The subform is in datasheet view. When the user clicks a record in the subform, I want to populate fields in the main form. In the subform's Click event, I currently have:

Public Sub Form_Click

Forms![ClientInfoForm]![Amendment] = Forms![ClientInfoForm]![EditTransactions_subform].Form![Amendment]

End Sub

I am not sure if this is the right code to execute what I want. The name of the field is [Amendment] in both the subform and the main form.

Thanks in advance for your help!

Howard

Skippy Skippy · Accepted Answer · 2017-02-11T14:00:21

This sounds back-to-front. The normal use of main form/sub-form is to navigate through your records on the main form and for each main record the sub-form will show all related child records. You sound as though you are always showing all your records from the child table in the sub-form and as you navigate through these you want the information in the main form to change.

If this is really what you want to do, and assuming these are bound forms, then you will need to make sure that there is no link between the Main and Sub-Form (on the sub-form properties clear the entries from Link Child Fields and Link Master Fields). The in the On Current event of the sub-form you could try something like this (although it's completely untested):

Dim rst As Recordset

Set rst = Me.Parent.RecordsetClone
rst.FindFirst "Amendment = '" & [Amendment] & "'"  'assumes [Amendment] is String, otherwise lose the surrounding quotes
Me.Parent.Bookmark = rst.Bookmark

rst.Close
Set rst = Nothing

Populate fields in main form from record in subform

1 Answers