Complex design problem

[BJones]

I have a series of 1 to many tables: Mission->Curriculum->Course-

Module->Lesson->Board. The really extensive work all needs to be

done on a form (itself having 4 non-nested subforms) that largely
represents the Board table. The Board records all have an identifier of
6 unique characters (eg DBAACA) each character of which represents a
record in the immediately preceding table (D for Mission, B for
Curriculum and so on), and the relationships are 1 to many (many
Boards in each Lesson and so on). The problem is exacerbated by the
fact that, if possible, I would like to use the one form for record editing
and adding for all or most of the tables.

I thought I could create a series of nested subforms to allow the right
dependencies to be expressed, but the form layout doesn't really work
with this many levels of nesting (and, I can't really get to all the records
at the lowest, most vital level since I have to use a single form view
with this many levels of nesting and 6 levels of navigation buttons is
not reasonable). I then thought that maybe I could build unbound
forms and create appropriate recordsets on the fly; that may be the way
to go, but I don't have the expertise with recordsets yet to do that.

Any suggestions? Maybe I need to learn more about creating
recordsets on the fly ....

 

[John Vinson]

I thought I could create a series of nested subforms to allow the right
dependencies to be expressed, but the form layout doesn't really work
with this many levels of nesting (and, I can't really get to all the records
at the lowest, most vital level since I have to use a single form view
with this many levels of nesting and 6 levels of navigation buttons is
not reasonable). I then thought that maybe I could build unbound
forms and create appropriate recordsets on the fly; that may be the way
to go, but I don't have the expertise with recordsets yet to do that.

Any suggestions?

It's possible to have two subforms side by side (or even on separate
Tab pages on a main form) which are *logically* nested - that is, the
linking field in the selected record of SubformA is the Master Link
Field of SubformB. As you move to a different record on subformA,
SubformB changes its recordset to match.

Simply use the *complete* form reference - e.g.

Forms!Mainform!SubformA.Form!Controlname

in the Master Link Field property of SubformB.

It's also possible to pass a linking field to a popup form by using
the WhereCondition argument of the Openform method to display existing
records - *AND* passing the same value in the OpenArgs property, and
setting the DefaultValue property of the linking field in the popup
form's Open event.