Form Design Dilema

[Jebuz]

Hi Everyone!

Just a quick question. I have tried many design approaches for how to set
up my forms to display the information I want to see but haven't gotten it
quite right. I have tables set up like a hierarchy. A 1->many B 1 -> many
C. I want to be able to see all data for tables A, B, and C. So say select
A, it shows the many records related to it in table B. Select a record in B
and it shows the many records related to it in C. I am having troubles
finding a good way to get a selection from B to populate any type of control
that shows related records in C. Any suggestions, or does this make any
sense?

Thanks

 

[Jeff Boyce]

If your goal is to find table C records and display them, one approach would
be to start out with a main form that holds two combo boxes. The first one
lets you pick a TableA record, then requeries the second. The second combo
box lets you pick a TableB record (but only for those that are "children" to
the selected TableA record). To find out how to do this, take a look for
"Cascading Combo boxes" (try mvps.org/access or Google.com).

Once these two are working, decide whether you want to pick/display a single
related TableC record, or all related TableC records. If the former, use
the cascading combobox route one more level. If the latter, consider
creating a form that holds TableC records, then embedding that form as a
subform in your main form (above). You'd use the contents of the first two
combo boxes to link to the corresponding fields for the subform to display
the correct records.

Regards

Jeff Boyce
Microsoft Office/Access MVP