Cascading Combo Box Help

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. 
0
Utf
1/2/2008 5:02:01 PM
access 16762 articles. 2 followers. Follow

9 Replies
498 Views

Similar Articles

[PageSpeed] 42

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

gives you a good example.

john

"mHalvy" <mHalvy@discussions.microsoft.com> schreef in bericht 
news:9C558A51-D079-47B5-8D87-B06AC14D1E45@microsoft.com...
>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. 


0
johnie
1/2/2008 5:13:45 PM
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.

"johnie@johnie.com" wrote:

> http://www.candace-tripp.com/download/cascadecombo2k.zip
> 
> gives you a good example.
> 
> john
> 
> "mHalvy" <mHalvy@discussions.microsoft.com> schreef in bericht 
> news:9C558A51-D079-47B5-8D87-B06AC14D1E45@microsoft.com...
> >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. 
> 
> 
> 
0
Utf
1/2/2008 5:59:06 PM
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/forum.aspx?nav=libraryMessages&tsn=1&tid=23626&webtag=ws-msdevapps
 

Ken Sheridan
Stafford, England

"mHalvy" wrote:

> 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.

0
Utf
1/2/2008 6:44:03 PM
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. 

"Ken Sheridan" wrote:

> 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/forum.aspx?nav=libraryMessages&tsn=1&tid=23626&webtag=ws-msdevapps
>  
> 
> Ken Sheridan
> Stafford, England
> 
> "mHalvy" wrote:
> 
> > 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.
> 
0
Utf
1/2/2008 7:13:00 PM
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.

"Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message 
news:217DBC6C-BE7A-40AE-A96A-2CF2A510C9F1@microsoft.com...
> 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/forum.aspx?nav=libraryMessages&tsn=1&tid=23626&webtag=ws-msdevapps
>
>
> Ken Sheridan
> Stafford, England
>
> "mHalvy" wrote:
>
>> 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.
> 


0
Pat
1/2/2008 7:57:25 PM
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


"mHalvy" <mHalvy@discussions.microsoft.com> wrote in message 
news:9C558A51-D079-47B5-8D87-B06AC14D1E45@microsoft.com...
>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. 


0
Pat
1/2/2008 8:06:09 PM
> 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


0
john
1/2/2008 8:56:43 PM
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

"mHalvy" wrote:

> 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. 
> 
> "Ken Sheridan" wrote:
> 
> > 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/forum.aspx?nav=libraryMessages&tsn=1&tid=23626&webtag=ws-msdevapps
> >  
> > 
> > Ken Sheridan
> > Stafford, England
> > 
> > "mHalvy" wrote:
> > 
> > > 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.
> >

0
Utf
1/2/2008 11:16:24 PM
Yep.  Thanks.
"john" <jo@hn.com> wrote in message news:flgtq5$qi8$1@textnews.wanadoo.nl...
>> 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
>
> 


0
Pat
1/3/2008 7:54:24 PM
Reply:

Similar Artilces:

Rules help #2
I receive emails from forty people each week as part of an office type football pool. After I get the mail I send a reply to say that I received it. All of the emails have picks in the subject line. I set up a rule to move all of these into a folder called football, works fine. The problem is when I send the reply I don't get a copy in my sent items folder. It shows up in the football folder. Is there a way to have my copy to go to the sent items folder? Outlook 2003 Thanks CR > The problem is when I send the reply I don't get a copy in my sent > items folder. It shows u...

Cascading combo boxes
I would like to set up a form containing a combo box with a list of mechanisms of injury. Some mechanisms have sub groups that I would like to have visible by means of linking them with a cascading combo box. Is it possible to have only some records from Cbo 1 link to Cbo 2? The examples I have seen to date show cascading data for all records in the first combo ie the city/state or postcode/suburb examples where every record has a sub record. Thanks for your help. -- Joe Joe, If I understand correctly, you initially show just the first combo (ex. cboInjury), and on ...

DPI Problem with Dialog Box
Hello All, I have a windows application running on WinNT/98 Platforms. I am facing the following problem with a Dialog Box. When I Change the display settings (System Display setting can be changed from the Display setting > setting > Advaced > DPI setting.) to 120 dpi or higher and reboot the computer and Starts my application. The dialog view goes out of order. Dialog does not fit into the screen and the controls on the dialog(Edit Control, Static Text etc) are wrongly positioned. The same dialog box appears fine on 96 dpi. Apart from this the rest of the application view works ...

New Outlook user needing help
After trying to sync my mobile phone & Pocket PC with Outlook 2000 on my computer the '+' prefix (i.e. + 44 1234 567890) which I have for all my telephone numbers do not show up in Outlook contacts. Even when I enter the '+' manually at the beginning of the number when I press save it disappears. What is most annoying of all is when I sync my phone with outlook again it even replaces all the numbers in my phone without the '+'. Can someone shed some light as to what settings I need to go into to resolve this problem...Many thanks ...

send&receive status box missing
Hi Y'all, The subject line kinda sez-it-all: my send & receive status box missing! I haven't done anything extraordinary in the past few days... I really do not know why it is not showing up, and I erally like it... If you have a good/very good idea to rectify this, please email me [gmalmos@oco.net] and (I would think) post it too... Thanking you in advance! \geo ps-yes, I have exhausted all normal type searches, already. It doesn't quite "sez-it-all" Like your Outlook version, for instance. It doesn't sez that. -- Russ Valentine [MVP-Outlook] "ge...

Font Size in Forms Combo Box
Is there any way to increase the font size of the displayed data in th forms Combo Box?? Et3rna -- Et3rna ----------------------------------------------------------------------- Et3rnal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=445 View this thread: http://www.excelforum.com/showthread.php?threadid=26307 Hi AFAIK you can't change this size -- Regards Frank Kabel Frankfurt, Germany Et3rnal wrote: > Is there any way to increase the font size of the displayed data in > the forms Combo Box?? > > Et3rnal ...

cascade copy?
Is there such a thing as a cascade copy? I know there's cascade delete where if you delete the main record, all related subrecords are deleted but what if I want to do the reverse. For example I have a quote that has subpackages related to it. If I want to copy the main quote, I want all related subrecords copied along. Or is this a programming thing? Thanks, Alan "Alan" <Alan@discussions.microsoft.com> wrote in message news:7A7551D8-B482-4911-9452-3FE801FED2D4@microsoft.com... > Is there such a thing as a cascade copy? I know there's cascade delete where ...

HELP!! Excel and PowPnt crash on startup in Office vx under 10.3.2
I'm getting crashes in Excel and PP whenever I try to run them. I'm running Panther 10.3.2 and have updated everything I can. Any suggestions? Thanks, Dan Dan Gordon <gojet@jetpropics.com> wrote: Hi Dan, > I'm getting crashes in Excel and PP whenever I try to run them. I'm > running Panther 10.3.2 and have updated everything I can. > Any suggestions? Try going to the preference folder in your user account and in the Microsoft folder, delete the Carbon registration database and the Office 10 font cache. These two files will be automatically re-create...

Insert Check Box
Can anyone tell me how to insert a simple check box in an Excel worksheet. I have the actual check box on my worksheet, but when I click it, I want a checkmark to appear...and that is not what it is doing. Please help. Krista, Then what DOES it do when you click it? Does it select the check box (put handles around it)? -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Krista Cook" <ekcook@comcast.net> wrote in message news:3E293822-F641-4DD5-844A-91459560293F@microsoft.com... > Can anyone tell me how to insert ...

Read Only dialogue box not always displaying...
Hello I have quite a few Excel 2000 workbooks that are stored out on a shared network drive. Normally, when a user attempts to open a workbook that another user already has open, it prompts them with the "Suchandsuch is locked for editing..." dialogue box with the read-only and notify options. However, not uncommonly this dialogue box isn't appearing when the previously mentioned scenario occurrs, leading the user to believe they have write access, which has lead to some headaches when one of the users goes to save their changes Is there something that universally causes this...

Cascade delete
Hi, I need to find out if the cascade delete option is set on any of the tables in my database. Any help is greatly appreciated. Cheers Mounilk Mounilk (mounilkadakia@hotmail.com) writes: > I need to find out if the cascade delete option is set on any of the > tables in my database. Any help is greatly appreciated. Look up the catalog view sys.foreign_keys in Books Online. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL S...

OWA 2003 Login Box
Without having to use Forms-Based Authentication, is there a way to add text in the default OWA account login box where it prompts for user name and password? Thanks Short answer: No. The only way to have a customized login page is with Forms-based authentication. -- Ben Winzenz Exchange MVP MessageOne "BrunoB" <brbrunetti@cvs.com> wrote in message news:1132285134.595762.191140@z14g2000cwz.googlegroups.com... > Without having to use Forms-Based Authentication, is there a way to add > text in the default OWA account login box where it prompts for user > name...

Budget Help
I have transaction information from January 2002 up to the present. I want to do a budget, however, everytime I try to create a budget there is no data from my transactions. It is like the autobudget function does not work at all. Any suggestions as to what I am doing wrong? ...

Help with Outlook 2003
I have way too many emails in OUTLOOK--about 9,000 or so and now cannot delete, nor download, and am basically dead in the water--any advise? Tool box repair kit was no help--Thanks for any reccomendations, K Kathy <anonymous@discussions.microsoft.com> wrote: > I have way too many emails in OUTLOOK--about 9,000 or so > and now cannot delete, nor download, and am basically > dead in the water--any advise? Tool box repair kit was > no help--Thanks for any reccomendations, The number of messages you have has no bearing on the problem at all. Why not post the exact text ...

Combo Box property
Does anyone know of a property or method for making a combo box execute some code when the box's list button is pressed? I've tried On Click, On DblClick, On Enter, On Update, etc, and the only thing that comes close is On Got Focus, but it only works once. Certain form controls need to be disabled whenever the list button is pressed, and then re-enabled when a list selection is made (On Update works for that). Programmatic suggestion are always welcome. Thanks Monty wrote: > Does anyone know of a property or method for making a combo box > execute some code when the box...

Help! How can I upload data and display it in Excel on client
side. Also, how can I open a book with several sheets on client side? (It is a web application). Thanks. ...

auto archive check box unknown effect
When looking at my AutoArchive settings in Outlook 2003, there is a check box under "During AutoArchive:" that says "Archive or delete old items." This doesn't make sense -- what is the effect of this check box? Does it mean it deletes items in some folders and archives others? Thanks! Eric I originally setup an MST file that didn't allow users to change the Outlook Cached More settings. I recently changed my mind about this, for Outlook users on a slower WAN link. The thing is, I have deployed Office 2003 via GPO. Redeploying doesn't fix this, nor d...

Grab info for text box in webpage
Hello, I am not sure I am asking this question in the correct forum. I have taken the Visio Chart and made a webpage from it. What I would like to do with the webpage is change it to a webpart to be placed onto a Sharepoint page. When you click on a certain image, I want specific data to be placed into textboxes on a different webpart. If I should be asking this in a different forum, please let me know which one. I know this involves different areas of programming. Thanks!! -- Eddi Rae If you are using the Visio Viewer then try: http://msdn.microsoft.com/en-us/library/cc297217.asp...

Combo box display
Hi all, I have a field that is a combo box. It works just as I'd like during data entry. However, when I put it on a form where I don't want to edit it, or on a report, I'd like it to look like any other text box. Right now it has the value highlighted and the two errors to the right of the value (to move up and down the list). Is it possible to change the display of the combo box so that it just shows the value? Thanks, Claire It looks like you have made the common mistake of using some of the fancy options Access provides but can cause you problems and co...

DATA LABEL HELP
I have a regular area chart and my data labels are several points away from where the value crosses the X axix.....in other words I need my data labels to move up each time the graph is drawn. I am using VBA to draw the graphs automatically - I know I can move the labels manually but since I have this fully automated, I need to automatically have the data labels sit on top of the value where the top of my area graph crosses the X axis...... PLEASE HELP! If the points have data labels, and you've used one of the standard positions for the labels (above, left, etc.), the data labels mov...

Help ! a couple of newbie questions
Hi I’m very new to excel. I’ve created a workbook that is being used to keep track of a small informal golf league. I’ve figured out how to calculate and keep an ongoing handicap (basically used the fill handle to keep the formula each time I add a new round). Now, my stupid little questions: 1. My handicap scores are kept in column H10 through H15. Each time I enter a new round (for example in row 16), the handicap score in H16 is updated to reflect the new revised handicap. What I’d like to do is have the new handicap number show up in my heading area in cell E1. Eventually there ...

Reconnect with existing email box
Hello, About a month ago we had to delete and recreate a user mailbox (SBS server 2003, exchange 2003). The old mailbox sits in the store with a red x (disconnected). The user has since used the new box and it has many items in it so we cannot delete it. The user now needs many emails recovered from the 'deleted' box. I created a temp user and attempted to 'reconnect' the mailbox to this account but I get the error 'OU=... this legacyExchangeDN value is already being used by <the user>'. Makes perfect sense, he has a new email box. Is there a way to get thi...

Can one macro serve several check boxes?
I put several check boxes on a chart and assigned different macro to each of them. All each macro is doing is hide-unhide a specific column. Say I have ckeck boxes "Weight", "Length" and "Width". Assigned macro A, B and C accordingly. When I click "Weight" checkbox it calls macro "A" which toggles "hidden" status for column "J". So all macro have same functionality, only for different columns. I would like to build a _single_ macro, capable to know which control box was clicked. So, all check boxes will be assign...

Cascaded forms?
HI, I have a Customer table and an Orders table. I have another table with PaymentSchedule table containing Instalment promises for each order. Ideally i d create a form where I can either select an exisitng customer or create a new customer. Then select an order or create a new order. Then enter or modify the instalment details. What s the best way i could achieve this? is there some form of cascaded forms i could create? Thanks Ramesh Hi Ramesh Link the tables using the primary field of Customer table. Create 2 new forms based on both table. Link the forms - again using th...

Regardgin check boxes in an excel column
Hi all, I have a simple doubt which I hope should be easy for you guys. I want to make a cloumn in Excel with all entires having a check box for yes/no. I find nothing in excel that will do this. Alternatively, I can even do with restricitng the user to put just "yes" or "no" and "Y" or "N". I tried putting this into the data validation tab but it doesn't seem to work too. Can anybody help me out? Vikram -- vikramjparekh ------------------------------------------------------------------------ vikramjparekh's Profile: http://www.excelforum...