Cascading Combo Box Help

[mHalvy]

I know there are several threads on this already, but I don't seem to
understand them (maybe due to lack of sleep). But anyway, I have a form, and
all works well on the form except my combo boxes. I have three right now that
need a fixin'. One combo box is Chapter Name, the next is Chapter Section and
the third is Chapter Sub-section. I also have a table called
lookupChapterDetails ( a lookup table) that contains all of the data to be
used in these combo boxes. Now my question is, when I select a chapter in
Chapter Name, how do I get the other combo boxes to populate accordingly,
i.e. I select chapter one, and the sections for chapter one appear in the
second combo box, then when I select a section, the sub-sections appear for
that section in the third box. I have read about requerying or something like
that, but don't totally understand. I don't have any queries for these boxes
as the row source, to my knowledge. Any help would be much appreciated.

 

[Guest]

http://www.candace-tripp.com/download/cascadecombo2k.zip

gives you a good example.

john

 

[mHalvy]

That sort of really didn't help, cause it didn't tell me the procedure for
doing so. I tried to figure it out from the example, but I believe I am
missing something.

 

[Ken Sheridan]

From what you say it appears that you have a single table with the chapter
names, sections and subsections in it. If so the table will contain a lot of
redundancy in that it will contain multiple rows which say in which chapter a
particular section is located, i.e. one row for each subsection of that
section. This leaves the table open to inconsistent data being entered.
Redundancy is eliminated by the process of normalization by decomposing the
table into three tables, Chapters, Sections and SubSections.

The Chapters table needs only one column, Chapter say, with the chapter
names as these will be unique. This column will be the primary key of the
table.

The Sections table will have a column, Section say, with the section names.
This can be the primary key if the section names are unique, but if the same
name could apply to sections in different chapters introduce a numeric
SectionID primary key column, e.g. an autonumber. The table will also
include a foreign key column, Chapter referencing the primary key of the
Chapters table.

The SubSections table will have a SubSection column, which, as with
Sections, can be the primary key if sub-section names are unique, or you can
introduce a numeric SubSectionID column as the key. It will also have a
foreign key column, either Section or SectionID depending on whether you've
used Section as the primary key of Sections or have introduced a numeric
SectionID column as the key.

Create relationships between Chapters and Sections, and between Sections and
SubSections and enforce referential integrity.

As regards the combo boxes I'll assume that 'natural' keys Section and
SubSection have been used rather than surrogate numeric keys. The chapter
combo box would have a RowSource:

SELECT Chapter FROM Chapters ORDER BY Chapter;

The Section combo box would have a RowSource which references the chapter
combo box as a parameter, e.g.

SELECT Section FROM Sections WHERE Chapter = Form!cboChapter ORDER BY Section;

where cboChapter is the name of the combo box. Note that as the combo boxes
are on the same form you can reference them by means of the Form property
rather than using a fully qualified reference such as
Forms!YourForm!cboChapter.

Similarly the sub-sections combo box's RowSource property will reference the
section combo box as a parameter, e.g.

SELECT SubSection FROM SubSections WHERE Section = Form!cboSection ORDER BY
SubSection;

To correlate the combo boxes when a selection is made, so that the next one
shows only items relevant to the selection made in the previous one, you
firstly need to requery the sections combo box in the AfterUpdate event
procedure of the chapter combo box:

Me.cboSection.Requery

And similarly the sub-sections combo box is requeried in the AfterUpdate
event procedure of the sections combo box:

Me.cboSubSection.Requery

If you are using these correlated combo boxes to enter data into a table you
only need a column for the sub-section in the table as once you know this you
know the section and chapter, so to have columns for all three in one table
would introduce redundancy. Only cboSubSection needs to be a bound control,
therefore; cboSection and cboChapter can be unbound. You'll find a demo of
how this can be handled in various ways in both single form view and
continuous form view at the following link. It uses the local administrative
areas of County, District and Parish in my area to demonstrate this, but the
principles are exactly the same with hierarchical data like yours:


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


Ken Sheridan
Stafford, England

 

[mHalvy]

okay, so I think I understand everything except when you talk about creating
the sections and sub sections table. I do have chapters that have the same
sections and subsections, as a matter of fact, all but one do. So I'm rather
befuddled as to what to do, I know I go about creating the table with an
autonumber as the unique ID, and then enter the sections. Do I enter the
section just once, or do I have to put it in the table for each chapter (3
times in this case). Then with the foreign key, I have never worked with
those (at least I don't think). Is that just another field in the table that
references another particular table? I understand everything else except
these few rather simple things, which is odd. Thanks so much for your help
though.

 

[Pat Hartman]

Ken,
Although adding an autonumber to the two subtables is correct for ease of
use, you didn't mention that the subtables will each need a unique index to
enforce the business rules. For example, the Combination of Chapter and
Section must be unique and the combination of SectionID and SubSection must
be unique.

 

[Pat Hartman]

Best practice is to use three separate tables as Ken has suggested to
eliminate redundency.

Each rowsource query needs criteria that refers to it's "parent" combo
except the first one.

Select Chapter from tblChapter
Order by Chapter;

Select Section from tblSection
Where Chapter = Forms!yourform!cboChapter
Order by Section;

Select SubSection from tblSubSection
Where Section = Forms!yourform!cboSectionID --- this needs to be the ID
field that you will add when you split the data into three tables. The ID
will be hidden and so the combo will only show the text value
Order by SubSection;

There is no need to build these queries in code since they do not change.
The example is more complicated than it needs to be.

In the Current event of the form you need:
Me.cboChapter.Requery
Me.cboSection.Requery
Me.cboSubSection.Requery

In the AfterUpdate event of the chapter combo you need:
Me.cboSection.Requery
Me.cboSubSection = null

In the AfterUpdate event of the section combo you need:
Me.cboSubSectionRequery

 

[john]

In the Current event of the form you need:

Me.cboChapter.Requery
Me.cboSection.Requery
Me.cboSubSection.Requery

In the AfterUpdate event of the chapter combo you need:
Me.cboSection.Requery
Me.cboSubSection = null

If you change the chapter combo's value, shouldn't the Section combo get the
value null as well (just in case there was already something entered in the
Chapter and Section combo)?

In the AfterUpdate event of the section combo you need:
Me.cboSubSectionRequery

Shouldn't the Subsection's combo get the value null here as well for the
same reason as mentioned above?

John

 

[Ken Sheridan]

Its true that a foreign key is a column (field) in a table which references
another table; more accurately it’s a column, or set of columns (keys can be
multi-column) which reference the primary key of another table. The table
on the 'one side' of the relationship is known as the referenced table, while
that on the 'many side' is known as the referencing table.

In your case Chapters is the referenced table in the relationship with
Sections, so the foreign key Chapter column in Sections references the
primary key Chapter column of Chapters. As chapter names are unique within
the Chapters table this text column can be used as the primary key, and
designated as such in table design view. This is what's known as a 'natural'
key. As section names can apply to different chapters you will have the same
Section value in different rows in Sections, but with a different value in
the Chapter column for each.

As the primary key of Sections you have a choice. The combination of
Section and Chapter values must be unique within the table, so these are
what's known as a 'candidate key'. You could therefore designated the two
columns as a composite primary key. Alternatively, and this is what most
people do for convenience, you could introduce a 'surrogate' key numeric
column such as an autonumber. Note, however, what Pat has said about the
need to index the Chapter and Section columns uniquely (in combination, not
individually) so as to prevent duplicate combinations of values being entered
in these columns.

When it comes to the SubSections table, as sub-section names are not unique,
you again have a choice with regard to the keys. If you have used the
Chapter and Section columns as the composite primary key of Sections you
would need to have an equivalent composite foreign key in SubSections made up
of Chapter and Section columns and the relationship would be on both sets of
columns. If, as is more likely, you' use a surrogate SectionID numeric
primary key for Sections then you need a numeric SectionID foreign key in
SubSections. Whether you use a two-column or single column foreign key, the
combination of the values in the foreign key and the SubSection column must
be unique within the table, so is a candidate key. It could therefore be
used as a composite two or three-column primary key, or if you introduce a
surrogate SubSectionID numeric primary key, the columns in combination should
be indexed uniquely.

If you use a numeric surrogate primary key then a combo box in which to
select values from the table will be set up rather differently as you need to
see the text value but the underlying value of the combo box needs to be the
numeric key value. This is done by hiding the numeric bound column so you
only see the text values. In the case of cboSection it would be set up with
properties like so:

RowSource: SELECT SectionID, Section FROM Sections WHERE Chapter =
Form!cboChapter ORDER BY Section;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

In the case of cboSubSection:

RowSource: SELECT SubSectionID, SubSection FROM SubSections WHERE
SectionID = Form!cboSection ORDER BY SubSection;

The other properties would be as for cboSection.

Note also what Pat and John have said about the need to set cboSection and
cboSubSection to Null in the AfterUpdate of cboChapter, and to set
cboSubSection to Null in the AfterUpdate event of cboSection, as well as
requerying the controls. If you do download my demo and look at the code in
the form's module you'll see that it does this.

BTW one area my demo addresses is the use of correlated combo boxes on
continuous forms where the values are hidden surrogate key columns. While
the approach described in this thread works well in single form view it only
works in continuous form view if natural keys are used. This is because,
with a surrogate key, the hidden value of the control in non-current rows
will not correspond to a value in the visible column where the combo box's
RowSource has been restricted by a selection in a referenced combo box in the
current row so that it excludes the row which provides the value of the combo
box in the non-current row. This can be overcome by using a 'hybrid' control
of a text box superimposed on a combo box, but while my demo shows how to do
this I don't recommend it, and prefer the use of a multi-column combo box,
which the demo also includes.

Ken Sheridan
Stafford, England

 

[Pat Hartman]

Yep. Thanks.

If you change the chapter combo's value, shouldn't the Section combo get
the value null as well (just in case there was already something entered
in the Chapter and Section combo)?


Shouldn't the Subsection's combo get the value null here as well for the
same reason as mentioned above?

John