Linked Child Fields

  • Thread starter Thread starter WiFiMAX
  • Start date Start date
W

WiFiMAX

How do I replicate the Link Child/Master fields
functionality between two forms if none of these forms
are subforms?

i.e. how do I automatically update the related records in
a form when I change to a new record in a main form?
 
Use the Current event of one for to sync the other one to the same record
(FindFirst in the Recordsetclone, and set Bookmark), and vice versa.

In A2000 and later, you may also be able to achieve it by setting the
Recordset of the secondary form to the Recordset of the first.
 
How do I replicate the Link Child/Master fields
functionality between two forms if none of these forms
are subforms?

i.e. how do I automatically update the related records in
a form when I change to a new record in a main form?
Click to expand...

With a fair bit of VBA code, unfortunately.

Note that the Master/Child subform relationship does considerably more
than what you describe: it also causes new records on the subform to
inherit the value of the master link field.

The simplest way to do what you describe is to put VBA code in the
"master" form's Current event to change the "child" form's
Recordsource; for example -

Private Sub Form_Current()
Dim strSQL As String
If Not Me.NewRecord Then ' can't link from a blank record
strSQL = "SELECT this, that, theother FROM childtable" _
& " WHERE childtable.LinkField = " & Me!MasterField
Forms!ChildForm.Recordsource = strSQL
End If
' set the Default property so new records stay in synch
' Default is a String property so surround it with quotes
Forms!ChildForm!txtChildField.DefaultValue = _
Chr(34) & Me!MasterField & Chr(34)
End Sub

Why not use a Subform? Let Access do the work for you!

John W. Vinson[MVP]
 
I've got a similar need to this one that I hope someone can help me with.
I've got a master form called search_results_form which is a continuous
form. The macro that opens this also opens the Christmas card summary form in
hidden mode with the reference number being synchronised. I then have a
button on the master form which restores the summary form and displays the
corresponding data to the record in the main form.
My problem is that I can view and edit the data in the summary form (also a
continuous form), but when I go to add data it won't work and wants to create
a new reference number as auto number. I have the additions property on the
summary form set to yes (it's 'no' on the master form, but it doesn't make
any difference when I change this).
John, in your reply you said that the master/child subform relationship also
causes new records on the subform to inherit the value of the master link
field. This is exactly what I want and I've achieved it in another form using
a subform. The reason that I'm not using a subform in this situation is
because I need my master form to be continuous - and can't find a way to make
subforms work in this situation.

Any ideas?
 
Back
Top