Form Design w/ hierarchy structure

[Jebuz]

Hi All,

I have a question about general form designs. I have a sort of hierarchical
structure to my tables, say tables A B C. A 1->m B. B 1->m C. I want to
display all data in one snapshot. So given a list of records for table A,
select 1 and show all related records from table B. Select now a record from
table B and it will show all related records from C.

I have tried many different ways with subforms (doesn't show which records
relate to what in tables B & C.) Combo box's and list boxes (doesn't
populate the records in C as always expected.)

Whats the best way to show all this data in Access? What controls would be
best for each table?

 

[Larry Linson]

Hi All,

I have a question about general form designs. I have a sort of
hierarchical
structure to my tables, say tables A B C. A 1->m B. B 1->m C. I want to
display all data in one snapshot. So given a list of records for table A,
select 1 and show all related records from table B. Select now a record
from
table B and it will show all related records from C.

I have tried many different ways with subforms (doesn't show which records
relate to what in tables B & C.) Combo box's and list boxes (doesn't
populate the records in C as always expected.)

Whats the best way to show all this data
in Access? What controls would be
best for each table?

Your description of it "not working" doesn't give detail, but you can show
the information in the ways you describe, and properly implemented, either
should work well.

If you have a main Form displaying Table A (or a Query against Table A), in
which there is a Subform Control embedding a Form displaying Table B (or a
Query against Table B), with MasterLinkFields and ChildLinkFields set to
corresponding Fields on the main Form and the Form in the Subform Control
and that embedded Form has a Subform Control embedding Table C (or a Query
against Table C), with MasterLinkFields and ChildLinkFields set to
corresponding Fields on the Form in the first level Subform Control and the
Form in the second level Subform Control, you should see what you described
as what you want to see.

Similarly, if you can display enough information to make the choices in
Combo or List Boxes, refer to the higher-level Combo or List Box in the Row
Source, and Requery the lower-level Combo or List Box in the After Update
event of each higher-level Combo or List Box, that, too, should work.

Knowing the right details to supply for remote debugging assistance isn't
easy, nor is it always easy to determine just what questions to ask when the
information isn't sufficient.

Larry Linson
Microsoft Access MVP

 

[Jebuz]

Thanks Larry!!

I tried the form within a form within a form type design and that work
pretty well! I think I will go this route rather than the combo box method.
When I did the combo way it would work until I changed the highest level
combo box, the values in the lowest level box would not change until I
selected the value for the box right above it in the hierarchy. If that
makes any sense?

But even if that doesn't, the important thing is now I am off and on my way
to designing the look and feel.

Thanks again!