Linking 2 Subforms with Another--surely there is a way

[6afraidbecause789]

Think school - students - discipline interventions - misbehaviors -
staff for this one....On a mainform frmStudentInterventions, I have
linked a subform (sfrmMisbehaviors) with another subform
(sfrmInterventions) with the Child and Master fields
"InterventionID." This is tested and works - as users click through
the continuous list of interventions, the continuous list of
misbehaviors changes per intervention. Now, I need to link another
subform (sfrmStaff) with the subform (sfrmInterventions), such as also
by linking the Child and Master fields "InterventionID." So, the list
of involved staff (could be 1) would be synchronized with the
interventions. When I load test data into the underlying join table,
I can see the appropriate staff for an intervention. But, the
synchronization/refreshing does will work like the subform of
misbehaviors. Can there be the same Master/Child links for 2 separate
subforms on a main form? Have I reached Access's limit? Is there a
work-around for this?
Thanks a lot!

[Phil Stanton]

Try on your staff subform
LinkChildField InternentionID
LinkMasterField frmsMisbehaviours.form!InterventionID

All depends on having the correct table structure and relationships. There
is normally not a limit on the subforms, and as far as I know they can be
cascaded down. If you have too many and too many comboboxes / listboxes you
can get a very misleading error of "Too many databases open"

Very confusing - Schools, Master, Child

HTH

Phil
<6a***************@gmail.comwrote in message
news:18**********************************@k30g2000 hse.googlegroups.com...

Think school - students - discipline interventions - misbehaviors -
staff for this one....On a mainform frmStudentInterventions, I have
linked a subform (sfrmMisbehaviors) with another subform
(sfrmInterventions) with the Child and Master fields
"InterventionID." This is tested and works - as users click through
the continuous list of interventions, the continuous list of
misbehaviors changes per intervention. Now, I need to link another
subform (sfrmStaff) with the subform (sfrmInterventions), such as also
by linking the Child and Master fields "InterventionID." So, the list
of involved staff (could be 1) would be synchronized with the
interventions. When I load test data into the underlying join table,
I can see the appropriate staff for an intervention. But, the
synchronization/refreshing does will work like the subform of
misbehaviors. Can there be the same Master/Child links for 2 separate
subforms on a main form? Have I reached Access's limit? Is there a
work-around for this?
Thanks a lot!

[6afraidbecause789]

Thanks for replying Phil,

Good suggestion, but that still did not appear to work. I'm going to
try it the other way--linking sfrmMisbehaviors to sfrmStaff, through
the common field of InterventionID; we'll see and I'll post back when
I can.
Neither of these two subforms, sfrmMisbehaviors and sfrmStaff, should
be dependent on each other; they should simply and separately be
linked to sfrmInterventions. The structure is correct - both are
based on jointables that allow for many-to-many relations with
tblInterventions.

Nick

[6afraidbecause789]

OK, problem solved - somewhat. I didn't have a "requery" in the right
spot. So, I put

Me.Parent![sfrmStaff].Requery

in the On Current event of sfrmInterventions.

So now, when a user goes from mainform record to record, the 3
subforms - sfrmInterventions, sfrmStaff, and sfrmMisbehaviors - are
all synchronized.

HOWEVER, when a user navigates through records too fast, the
"Calculating..." message appears in the Status bar for a number of
seconds.

This is how the subforms are linked:

Mainform name = frmInterventions, pk = ClientID

1st subform name = sfrmInterventions, pk = InterventionID
Link Child Fields = StudentID
Link Master Fields = StudentID

2nd subform name = sfrmStaff
Link Child Fields = InterventionID
Link Master Fields = [sfrmInterventions].Form![InterventionID]

3rd subform name = sfrmMisbehaviors
Link Child Fields = InterventionID (same link as the
2nd subform)
Link Master Fields = [sfrmInterventions].Form![InterventionID]

When a user goes too fast, or if the 1st subform happens to have a lot
of data (in continuous view) when clicked into, the main form takes
awhile to refresh.

On a side note.....when I put yet another requery in the On Current
event of sfrmInterventions:

Me.Parent![sfrmStaff].Requery
Me.Parent![sfrmMisbehaviors].Requery

the "Calculating..." message appears - but never goes away - appears
to continuously loop and ultimately will lock up Access. I even see
the little subforms, sfrmStaff and sfrmMisbehaviors, alternate in
flickering for a few seconds each. I've corrected the form fields'
conditional formatting, as described by Allen Browne at
http://allenbrowne.com/bug-05.html.

So, for closers, can Access handle these three subforms on a main
form??? Is there a way to make this more efficient?

Thanks, and have a good day.