Related Tables

[Anita Taylor]

I have three tables - One for Classification, one for Sub-Classification and
one for Sub-Sub Classification. I need these to ultimately be three separate
fields on a form, but cannot figure out how to relate the tables. In the
Sub-Class table, I need to refer back to the Classification. Only certain
Sub-Classes match up with each Classification. In the Sub-Sub Class table,
only certain ones match up with with certain Sub-Classes, which depends upon
what Classification was selected -- does that make sense?

I know I'm looking at cascading drop-downs in the form, but right now I'm
trying to get it to work in the table - in fact, if I can get it to work in
the table, I wouldn't need the form at all. Users could open the table and
select a Classification. The Sub-Class field would then show only appropriate
entries based on the selected Classification. The Sub-Sub Class field would
also only show appropriate entries based on the Sub-Class selected.

I'm pulling data from another source and it's already set up this way. I
don't want the other person helping me on this project to have to manually
pick through well over 100 permutations if I combined all three
categorizations into one table. I actually need the data in individual,
separate fields for an update query later on.

This can be ugly - I don't need it to be fancy because it's only for me and
one other person to use and we've got to re-code about 7,000 incorrectly
coded items. We just need a way to tunnel down (cascade) entries into three
different fields.

Any help would be greatly appreciated and keep us from going cross-eyed!

 

[Ken Sheridan]

The SubSubClassifications table is related to the SubClassifications table on
SubClassification, and the SubClassifications table is related to the
Classifications table on Classification.

You should only have a SubSubClassification column in your referencing table
(the table the form is based on). The SubSubClassification tells you what
the SubClassification is, and the SubClassification tells you what the
Classification is, so to have all three columns in the referencing table
would introduce redundancy. This is not just wasteful, but opens up the
table to inconsistent data being entered. To return the data in three
columns you just have to join the tables and include the relevant columns in
the query's result table.

As it happens I posted a demo file for handling this sort of hierarchical
data with correlated combo boxes at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The demo uses geographical data, the local administrative units of Parish,
District and County in my area, but the principle is exactly the same with
Parish equating to your SubSubClassification, District to your
SubClassification and County to your Classification. You shouldn't find it
too difficult to translate it to your own set-up, but if you have any
problems feel free to mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

BTW when you say 'if I can get it to work in the table, I wouldn't need the
form at all' you'll upset many people. One of the most frequent pieces of
advice given here is never, ever allow users to enter or edit data in a table
in raw datasheet view, but always in a form.

Ken Sheridan
Stafford, England

 

[Anita Taylor]

This is actually working very well and, as you said, is easy to follow. In
your example, I've made the following equations to my own database:

Location = Ticket
County = Class
District = SubClass
Parish = SubSub

Whenever I try to select from my first combo box (Class), I get the
following error: Run-time error '2448': You can't assign a value to this
object.

When I debug, the line that's highlighted is: Me!cboSubSub = Null

Here's the entire code for the AfterUpdate action for this combo:

Private Sub cboClass_AfterUpdate()
' if Class is updated then erase current values
' for SubClass and SubSub and requery combo boxes
' to show SubClass of selected Class and empty list
' of SubSub (pending selection of a SubClass)
Me!cboSubClass = Null
Me!cboSubSub = Null
Me!cboSubClass.Requery
Me!cboSubSub.Requery

End Sub

Any ideas?