Requery One Subform Based on Current Record in Another Subform

[Guest]

I have a main form (Documents) that contains two subforms (Revisions and
sfNotes). Both subforms are based on the same Revisions table; however, since
Notes is a memo field and needs more room than the first subform allowed, I'd
like to place it in a different location (on a second subform) on the main
form.

The trick is that, as a new record is selected in the Revisions subform, I
want the sfNotes subform to synchronize so it displays the notes for that
same record. I have the following code in the OnCurrent event of the
Revisions subform:
Private Sub Form_Current()
Forms![Documents]![sfNotes].Requery
End Sub

However, it's not refreshing the sfNotes subform. What am I doing wrong?

Thanks,
Jen

 

[Marshall Barton]

I have a main form (Documents) that contains two subforms (Revisions and
sfNotes). Both subforms are based on the same Revisions table; however, since
Notes is a memo field and needs more room than the first subform allowed, I'd
like to place it in a different location (on a second subform) on the main
form.

The trick is that, as a new record is selected in the Revisions subform, I
want the sfNotes subform to synchronize so it displays the notes for that
same record. I have the following code in the OnCurrent event of the
Revisions subform:
Private Sub Form_Current()
Forms![Documents]![sfNotes].Requery
End Sub

However, it's not refreshing the sfNotes subform. What am I doing wrong?

That would work if the notes subform's record source query
has a criteria referring back to the current record's PK
field in the Revisions subform.

A better way is to add a text box (named txtLink) to the
main form's header section. Then use the Revisions
subform's Current event to set it's value:
Parent.txtLink = Me.pkfield

Then you can use the subform control's Link Master/Child
Fields properties to synchronize the two subforms.

Link Master: txtLink
Link Child: pkfield

 

[Guest]

Worked like a charm - thanks so much Marsh!!

I have a main form (Documents) that contains two subforms (Revisions and
sfNotes). Both subforms are based on the same Revisions table; however, since
Notes is a memo field and needs more room than the first subform allowed, I'd
like to place it in a different location (on a second subform) on the main
form.

The trick is that, as a new record is selected in the Revisions subform, I
want the sfNotes subform to synchronize so it displays the notes for that
same record. I have the following code in the OnCurrent event of the
Revisions subform:
Private Sub Form_Current()
Forms![Documents]![sfNotes].Requery
End Sub

However, it's not refreshing the sfNotes subform. What am I doing wrong?

That would work if the notes subform's record source query
has a criteria referring back to the current record's PK
field in the Revisions subform.

A better way is to add a text box (named txtLink) to the
main form's header section. Then use the Revisions
subform's Current event to set it's value:
Parent.txtLink = Me.pkfield

Then you can use the subform control's Link Master/Child
Fields properties to synchronize the two subforms.

Link Master: txtLink
Link Child: pkfield