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

[6afraidbecause789]

Think school - students - discipline interventions - misbehaviors -
staff for this one....On a mainform frmStudentInter ventions, I have
linked a subform (sfrmMisbehavio rs) with another subform
(sfrmInterventi ons) 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 (sfrmInterventi ons), 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 frmsMisbehaviou rs.form!Interve ntionID

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.comwr ote in message
news:18******** *************** ***********@k30 g2000hse.google groups.com...

Think school - students - discipline interventions - misbehaviors -
staff for this one....On a mainform frmStudentInter ventions, I have
linked a subform (sfrmMisbehavio rs) with another subform
(sfrmInterventi ons) 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 (sfrmInterventi ons), 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 sfrmMisbehavior s to sfrmStaff, through
the common field of InterventionID; we'll see and I'll post back when
I can.
Neither of these two subforms, sfrmMisbehavior s and sfrmStaff, should
be dependent on each other; they should simply and separately be
linked to sfrmInterventio ns. The structure is correct - both are
based on jointables that allow for many-to-many relations with
tblIntervention s.

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 sfrmInterventio ns.

So now, when a user goes from mainform record to record, the 3
subforms - sfrmInterventio ns, sfrmStaff, and sfrmMisbehavior s - 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 = frmIntervention s, pk = ClientID

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

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

3rd subform name = sfrmMisbehavior s
Link Child Fields = InterventionID (same link as the
2nd subform)
Link Master Fields = [sfrmInterventio ns].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 sfrmInterventio ns:

Me.Parent![sfrmStaff].Requery
Me.Parent![sfrmMisbehavior s].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 sfrmMisbehavior s, 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.