Linked Comboboxes. Breakdown in levels.

[Nicola M]

Hi all! Access 2003.
Probably this issue is due to an incorrect database design phase but I post
here because I find the problem building a form.
I have a series of data logically connected each other that I manage with
linked comboboxes (maybe this isn't the right definition). I have a
cboSector, a cboBranch, a cboOffice, a cboDuty and so on. Right now, writing
code, I discover that for some path I have 4 levels but for other I have 5 or
6 levels thus I need 1 or 2 other cbos more. The question is about tha way to
manage this issue.
1) For a 4 level path there's no problem;
2) For a 5/6 level path I'm thinking to show/hide the 5th and the 6th cbos
programatically when I need them.
This approach unfortunately cause a lot of empty fields in the 4 level path
records and some empty fields in the 5 level path records. Designing best
practises allow this or not? There's a better way to follow?
Thank you in advance for your cooperation.

Nicola.

PS.
Hope the explanation is clear also.

 

[Steve]

You need tables for the six levels which you probably have. Consider this
design:
TblMainobject
MainObjectID
SectorID
BranchID
OfficeID
DutyID

TblLevel5AndLevel6
Level5AndLevel6ID
MainObjectID
Level5ID
Level6ID

Record all your main objects in TblMainobject. Record only your main objects
that have level5 and or level 6 in
TblLevel5AndLevel6. To do this, use a form/subform. Base the main form on
TblMainobject and base the subform on TblLevel5AndLevel6. Use a query that
includes both tables as the recordsource for any forms and reports where you
want to display the data from both tables.

Steve