Need help with cascading combos

Hello:

I have posted multiple threads in this forum. I have received valuable
feedback for my questions. Unfortunately, I have not been able to get my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope that's not considered
unpolite). Maybe this will illustrate what I'm trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected in the combo boxes)
- tblBoards (resides on a mainform - values are independent from the
cascading combos)
- tblDivisions (values are "parent" to values in tblSections)
- tblSections (values are "parent" to values in tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier (Division to Sections to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value of "Board 1" plus the subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section) resides
- Subsubsubform: this is where 3rd tier (BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division" on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections" on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3 tiers don't work. Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom

It is normally NOT polite to send the binary file without someone requesting
it first. Many folks have slow, dial-up connections and don't want to have
to wait for the download. Also, the potential for viruses exists. The file
was small and I had a non critical computer I could open it on, so I took a
look. The problem is that you need to refer to the subform control on each
form. The subform control is a control that holds a subform. A subform is
not open in it own right and so the name of the subform doesn't get you
anywhere, you have to follow the path down from the main form. I modified
the Row Source on the Division, Sections, and Billet Codes combos as follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!Di vision)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!Di vision) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!Sub form.Form!Sections)) ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I have received valuable
feedback for my questions. Unfortunately, I have not been able to get my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope that's not considered
unpolite). Maybe this will illustrate what I'm trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected in the combo boxes)
- tblBoards (resides on a mainform - values are independent from the
cascading combos)
- tblDivisions (values are "parent" to values in tblSections)
- tblSections (values are "parent" to values in tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier (Division to Sections to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value of "Board 1" plus the subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section) resides
- Subsubsubform: this is where 3rd tier (BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division" on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections" on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3 tiers don't work. Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom

Wayne:

Thanks so much for your reply and the help you've given me on this. I also
appreciate your sharing the courtesy procedures used in this forum.

I made the changes to the sample db, and it works fine. Now I am
translating the same structure of "code" into my actual db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk) INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID = tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary key, foreign keys,
etc. You didn't use them in the Row Source statement. Do I need to modify
the RowSource to accomodate the relationships? Or isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning on the 2nd tier
level - Sections) that prompt me to enter the "Division" ... even though I
already selected the Division from the combo. Why is that? What would I
need to modify to make it work?

I hope you'll have another chance to provide me more pointers so that I can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan" wrote in message
...
It is normally NOT polite to send the binary file without someone
requesting
it first. Many folks have slow, dial-up connections and don't want to have
to wait for the download. Also, the potential for viruses exists. The file
was small and I had a non critical computer I could open it on, so I took
a
look. The problem is that you need to refer to the subform control on each
form. The subform control is a control that holds a subform. A subform is
not open in it own right and so the name of the subform doesn't get you
anywhere, you have to follow the path down from the main form. I modified
the Row Source on the Division, Sections, and Billet Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!Di vision)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!Di vision) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!Sub form.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I have received valuable
feedback for my questions. Unfortunately, I have not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope that's not
considered
unpolite). Maybe this will illustrate what I'm trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected in the combo boxes)
- tblBoards (resides on a mainform - values are independent from the
cascading combos)
- tblDivisions (values are "parent" to values in tblSections)
- tblSections (values are "parent" to values in tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier (Division to Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section) resides
- Subsubsubform: this is where 3rd tier (BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division" on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections" on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3 tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom

1) If you refer to the query in the row source, then the query already does
the joins for you. Basing a query on a query is legal.

2) The modifications I made were to get rid of the pop-ups and did when I
tested it. The pop-ups happen when a query, either the one in the row source
or the query it's based on, have parameters that it can't "see". This would
happen if you mistype it (i.e. spelling error, syntax error, etc) or if the
form being used as the source of the data is closed.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for your reply and the help you've given me on this. I
also
appreciate your sharing the courtesy procedures used in this forum.

I made the changes to the sample db, and it works fine. Now I am
translating the same structure of "code" into my actual db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk) INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID = tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary key, foreign keys,
etc. You didn't use them in the Row Source statement. Do I need to
modify
the RowSource to accomodate the relationships? Or isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning on the 2nd tier
level - Sections) that prompt me to enter the "Division" ... even though I
already selected the Division from the combo. Why is that? What would
I
need to modify to make it work?

I hope you'll have another chance to provide me more pointers so that I
can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan" wrote in
message
...
It is normally NOT polite to send the binary file without someone
requesting
it first. Many folks have slow, dial-up connections and don't want to
have
to wait for the download. Also, the potential for viruses exists. The
file
was small and I had a non critical computer I could open it on, so I
took
a
look. The problem is that you need to refer to the subform control on
each
form. The subform control is a control that holds a subform. A subform
is
not open in it own right and so the name of the subform doesn't get you
anywhere, you have to follow the path down from the main form. I
modified
the Row Source on the Division, Sections, and Billet Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!Di vision)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!Di vision) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!Sub form.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I have received
valuable
feedback for my questions. Unfortunately, I have not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope that's not
considered
unpolite). Maybe this will illustrate what I'm trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected in the combo boxes)
- tblBoards (resides on a mainform - values are independent from the
cascading combos)
- tblDivisions (values are "parent" to values in tblSections)
- tblSections (values are "parent" to values in tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier (Division to
Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section) resides
- Subsubsubform: this is where 3rd tier (BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division" on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections" on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3 tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom

Wayne:

Thanks... I'll look into the syntax (I'm away from my PC
where I stored the sample file).

Meanwhile, I realized that "refreshing" does not always
work properly.

For instance, the 1st time I e.g.
select "A", "AA", "AAA". All values were updated properly.

However, then, if I were to switch to a different
division "B" I sometimes still see "A?" or "A??" for the
division and sections, respectively.


Any additional pointers?


Tom


-----Original Message-----
1) If you refer to the query in the row source, then the
query already does
the joins for you. Basing a query on a query is legal.

2) The modifications I made were to get rid of the pop-
ups and did when I
tested it. The pop-ups happen when a query, either the
one in the row source
or the query it's based on, have parameters that it
can't "see". This would
happen if you mistype it (i.e. spelling error, syntax
error, etc) or if the
form being used as the source of the data is closed.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for your reply and the help you've given
me on this. I
also
appreciate your sharing the courtesy procedures used in
this forum.

I made the changes to the sample db, and it works
fine. Now I am
translating the same structure of "code" into my actual
db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code
suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER
JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID =
tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary
key, foreign keys,
etc. You didn't use them in the Row Source
statement. Do I need to
modify
the RowSource to accomodate the relationships? Or
isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning
on the 2nd tier
level - Sections) that prompt me to enter
the "Division" ... even though I
already selected the Division from the combo. Why is
that? What would
I
need to modify to make it work?

I hope you'll have another chance to provide me more
pointers so that I
can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan"
wrote in
message
...
It is normally NOT polite to send the binary file
without someone
requesting
it first. Many folks have slow, dial-up connections
and don't want to
have
to wait for the download. Also, the potential for
viruses exists. The
file
was small and I had a non critical computer I could
open it on, so I
took
a
look. The problem is that you need to refer to the
subform control on
each
form. The subform control is a control that holds a
subform. A subform
is
not open in it own right and so the name of the
subform doesn't get you
anywhere, you have to follow the path down from the
main form. I
modified
the Row Source on the Division, Sections, and Billet
Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY
qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!
Division)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!
Division) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!
Subform.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I
have received
valuable
feedback for my questions. Unfortunately, I have
not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope
that's not
considered
unpolite). Maybe this will illustrate what I'm
trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected
in the combo boxes)
- tblBoards (resides on a mainform - values are
independent from the
cascading combos)
- tblDivisions (values are "parent" to values in
tblSections)
- tblSections (values are "parent" to values in
tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier
(Division to
Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value
of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section)
resides
- Subsubsubform: this is where 3rd tier
(BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division"
on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections"
on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3
tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom














.

After you make a change to the upper level combo(s), requery the next lower
level combo. You may also want to set the value of the next lower level
combo to Null to clear the previous selection.

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Thanks... I'll look into the syntax (I'm away from my PC
where I stored the sample file).

Meanwhile, I realized that "refreshing" does not always
work properly.

For instance, the 1st time I e.g.
select "A", "AA", "AAA". All values were updated properly.

However, then, if I were to switch to a different
division "B" I sometimes still see "A?" or "A??" for the
division and sections, respectively.


Any additional pointers?


Tom


-----Original Message-----
1) If you refer to the query in the row source, then the
query already does
the joins for you. Basing a query on a query is legal.

2) The modifications I made were to get rid of the pop-
ups and did when I
tested it. The pop-ups happen when a query, either the
one in the row source
or the query it's based on, have parameters that it
can't "see". This would
happen if you mistype it (i.e. spelling error, syntax
error, etc) or if the
form being used as the source of the data is closed.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for your reply and the help you've given
me on this. I
also
appreciate your sharing the courtesy procedures used in
this forum.

I made the changes to the sample db, and it works
fine. Now I am
translating the same structure of "code" into my actual
db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code
suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER
JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID =
tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary
key, foreign keys,
etc. You didn't use them in the Row Source
statement. Do I need to
modify
the RowSource to accomodate the relationships? Or
isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning
on the 2nd tier
level - Sections) that prompt me to enter
the "Division" ... even though I
already selected the Division from the combo. Why is
that? What would
I
need to modify to make it work?

I hope you'll have another chance to provide me more
pointers so that I
can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan"
wrote in
message
...
It is normally NOT polite to send the binary file
without someone
requesting
it first. Many folks have slow, dial-up connections
and don't want to
have
to wait for the download. Also, the potential for
viruses exists. The
file
was small and I had a non critical computer I could
open it on, so I
took
a
look. The problem is that you need to refer to the
subform control on
each
form. The subform control is a control that holds a
subform. A subform
is
not open in it own right and so the name of the
subform doesn't get you
anywhere, you have to follow the path down from the
main form. I
modified
the Row Source on the Division, Sections, and Billet
Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY
qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!
Division)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!
Division) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!
Subform.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I
have received
valuable
feedback for my questions. Unfortunately, I have
not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope
that's not
considered
unpolite). Maybe this will illustrate what I'm
trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected
in the combo boxes)
- tblBoards (resides on a mainform - values are
independent from the
cascading combos)
- tblDivisions (values are "parent" to values in
tblSections)
- tblSections (values are "parent" to values in
tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier
(Division to
Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value
of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section)
resides
- Subsubsubform: this is where 3rd tier
(BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division"
on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections"
on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3
tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom














.

Wayne:

I have tried to requery the next level subforms... unsuccessfully though.

Me.Requery won't do it, right? Also, I don't know how to set the next
lower level to Null. Would you please provide me more info for that?

--
Thanks,
Tom


"Wayne Morgan" wrote in message
...
After you make a change to the upper level combo(s), requery the next
lower
level combo. You may also want to set the value of the next lower level
combo to Null to clear the previous selection.

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Thanks... I'll look into the syntax (I'm away from my PC
where I stored the sample file).

Meanwhile, I realized that "refreshing" does not always
work properly.

For instance, the 1st time I e.g.
select "A", "AA", "AAA". All values were updated properly.

However, then, if I were to switch to a different
division "B" I sometimes still see "A?" or "A??" for the
division and sections, respectively.


Any additional pointers?


Tom


-----Original Message-----
1) If you refer to the query in the row source, then the
query already does
the joins for you. Basing a query on a query is legal.

2) The modifications I made were to get rid of the pop-
ups and did when I
tested it. The pop-ups happen when a query, either the
one in the row source
or the query it's based on, have parameters that it
can't "see". This would
happen if you mistype it (i.e. spelling error, syntax
error, etc) or if the
form being used as the source of the data is closed.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for your reply and the help you've given
me on this. I
also
appreciate your sharing the courtesy procedures used in
this forum.

I made the changes to the sample db, and it works
fine. Now I am
translating the same structure of "code" into my actual
db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code
suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER
JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID =
tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary
key, foreign keys,
etc. You didn't use them in the Row Source
statement. Do I need to
modify
the RowSource to accomodate the relationships? Or
isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning
on the 2nd tier
level - Sections) that prompt me to enter
the "Division" ... even though I
already selected the Division from the combo. Why is
that? What would
I
need to modify to make it work?

I hope you'll have another chance to provide me more
pointers so that I
can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan"
wrote in
message
...
It is normally NOT polite to send the binary file
without someone
requesting
it first. Many folks have slow, dial-up connections
and don't want to
have
to wait for the download. Also, the potential for
viruses exists. The
file
was small and I had a non critical computer I could
open it on, so I
took
a
look. The problem is that you need to refer to the
subform control on
each
form. The subform control is a control that holds a
subform. A subform
is
not open in it own right and so the name of the
subform doesn't get you
anywhere, you have to follow the path down from the
main form. I
modified
the Row Source on the Division, Sections, and Billet
Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY
qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!
Division)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!
Division) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!
Subform.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I
have received
valuable
feedback for my questions. Unfortunately, I have
not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope
that's not
considered
unpolite). Maybe this will illustrate what I'm
trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected
in the combo boxes)
- tblBoards (resides on a mainform - values are
independent from the
cascading combos)
- tblDivisions (values are "parent" to values in
tblSections)
- tblSections (values are "parent" to values in
tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier
(Division to
Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value
of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section)
resides
- Subsubsubform: this is where 3rd tier
(BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division"
on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections"
on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3
tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom














.

You will need to use the syntax mention before to refer to a control on a
subform.

Me.Subform.Form.cboCombobox = Null
Me.Subform.Form.cboCombobox.Requery

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

I have tried to requery the next level subforms... unsuccessfully though.

Me.Requery won't do it, right? Also, I don't know how to set the next
lower level to Null. Would you please provide me more info for that?

--
Thanks,
Tom


"Wayne Morgan" wrote in
message
...
After you make a change to the upper level combo(s), requery the next
lower
level combo. You may also want to set the value of the next lower level
combo to Null to clear the previous selection.

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Thanks... I'll look into the syntax (I'm away from my PC
where I stored the sample file).

Meanwhile, I realized that "refreshing" does not always
work properly.

For instance, the 1st time I e.g.
select "A", "AA", "AAA". All values were updated properly.

However, then, if I were to switch to a different
division "B" I sometimes still see "A?" or "A??" for the
division and sections, respectively.


Any additional pointers?


Tom


-----Original Message-----
1) If you refer to the query in the row source, then the
query already does
the joins for you. Basing a query on a query is legal.

2) The modifications I made were to get rid of the pop-
ups and did when I
tested it. The pop-ups happen when a query, either the
one in the row source
or the query it's based on, have parameters that it
can't "see". This would
happen if you mistype it (i.e. spelling error, syntax
error, etc) or if the
form being used as the source of the data is closed.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for your reply and the help you've given
me on this. I
also
appreciate your sharing the courtesy procedures used in
this forum.

I made the changes to the sample db, and it works
fine. Now I am
translating the same structure of "code" into my actual
db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code
suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER
JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID =
tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary
key, foreign keys,
etc. You didn't use them in the Row Source
statement. Do I need to
modify
the RowSource to accomodate the relationships? Or
isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning
on the 2nd tier
level - Sections) that prompt me to enter
the "Division" ... even though I
already selected the Division from the combo. Why is
that? What would
I
need to modify to make it work?

I hope you'll have another chance to provide me more
pointers so that I
can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan"
wrote in
message
...
It is normally NOT polite to send the binary file
without someone
requesting
it first. Many folks have slow, dial-up connections
and don't want to
have
to wait for the download. Also, the potential for
viruses exists. The
file
was small and I had a non critical computer I could
open it on, so I
took
a
look. The problem is that you need to refer to the
subform control on
each
form. The subform control is a control that holds a
subform. A subform
is
not open in it own right and so the name of the
subform doesn't get you
anywhere, you have to follow the path down from the
main form. I
modified
the Row Source on the Division, Sections, and Billet
Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY
qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!
Division)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!
Division) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!
Subform.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I
have received
valuable
feedback for my questions. Unfortunately, I have
not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope
that's not
considered
unpolite). Maybe this will illustrate what I'm
trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected
in the combo boxes)
- tblBoards (resides on a mainform - values are
independent from the
cascading combos)
- tblDivisions (values are "parent" to values in
tblSections)
- tblSections (values are "parent" to values in
tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier
(Division to
Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value
of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section)
resides
- Subsubsubform: this is where 3rd tier
(BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division"
on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections"
on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3
tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom














.

Wayne:

This subject "requery" on subforms is (in my opinion) a very abstract topic.

I have tried to follow your logic but it doesn't seem to work for me.
Please be patient w/ me.

Okay, here's I guess where my trouble lies:
1. No combo boxes are being used on the "Mainform". Therefore I believe
that this entire enity (field, controls, etc.) can be taken out of the
picture, right?

2. If I open up the "Mainform" I now see all of the underlying subforms.
If I click on the 1st level of subforms (Division), I can see either a) an
symbol shaped like a fist + plus all of the "handles" where I could resize
the form... or b) I see a small gray square and when I click on it I see a
black bullet/dot in that.

If I select 2a, I see that "Subform" is listed on both the "Data" and the
"Other" tab.
If I select 2b, I only see "Subform" listed under the "Format" tab.

3. Then If I click on the actual combo box "Division" that sits on "Subform"
I see "Division" under "Data" and "Other" tab.


I am as confident as I can be that I have followed your guidelines for
requerying one combo based on its parent combo. Still, I get errors that
indicated that "something" counldn't be found.


Given the naming convention of
- Mainform (general data)
- Subform (Division combo)
- Subsubform (Sections combo)
- Subsubsubform (BilletCode)

If changing the combobox "Division" on "Subform" (1st subform layer), the
following doesn't work for me:

Me.Subsubform.Form.Sections = Null
Me.Subsubform.Form.Sections.Requery


Is it safe to safe that "Me" = "Subform".


At this moment, I have tried all versions I could think of. I know it's
me, but I can't understand the structure of this. Please bear w/ me on
this.


Thanks again,
Tom




"Wayne Morgan" wrote in message
...
You will need to use the syntax mention before to refer to a control on a
subform.

Me.Subform.Form.cboCombobox = Null
Me.Subform.Form.cboCombobox.Requery

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

I have tried to requery the next level subforms... unsuccessfully
though.

Me.Requery won't do it, right? Also, I don't know how to set the next
lower level to Null. Would you please provide me more info for that?

--
Thanks,
Tom


"Wayne Morgan" wrote in
message
...
After you make a change to the upper level combo(s), requery the next
lower
level combo. You may also want to set the value of the next lower
level
combo to Null to clear the previous selection.

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Thanks... I'll look into the syntax (I'm away from my PC
where I stored the sample file).

Meanwhile, I realized that "refreshing" does not always
work properly.

For instance, the 1st time I e.g.
select "A", "AA", "AAA". All values were updated properly.

However, then, if I were to switch to a different
division "B" I sometimes still see "A?" or "A??" for the
division and sections, respectively.


Any additional pointers?


Tom


-----Original Message-----
1) If you refer to the query in the row source, then the
query already does
the joins for you. Basing a query on a query is legal.

2) The modifications I made were to get rid of the pop-
ups and did when I
tested it. The pop-ups happen when a query, either the
one in the row source
or the query it's based on, have parameters that it
can't "see". This would
happen if you mistype it (i.e. spelling error, syntax
error, etc) or if the
form being used as the source of the data is closed.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for your reply and the help you've given
me on this. I
also
appreciate your sharing the courtesy procedures used in
this forum.

I made the changes to the sample db, and it works
fine. Now I am
translating the same structure of "code" into my actual
db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code
suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER
JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID =
tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary
key, foreign keys,
etc. You didn't use them in the Row Source
statement. Do I need to
modify
the RowSource to accomodate the relationships? Or
isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning
on the 2nd tier
level - Sections) that prompt me to enter
the "Division" ... even though I
already selected the Division from the combo. Why is
that? What would
I
need to modify to make it work?

I hope you'll have another chance to provide me more
pointers so that I
can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan"
wrote in
message
...
It is normally NOT polite to send the binary file
without someone
requesting
it first. Many folks have slow, dial-up connections
and don't want to
have
to wait for the download. Also, the potential for
viruses exists. The
file
was small and I had a non critical computer I could
open it on, so I
took
a
look. The problem is that you need to refer to the
subform control on
each
form. The subform control is a control that holds a
subform. A subform
is
not open in it own right and so the name of the
subform doesn't get you
anywhere, you have to follow the path down from the
main form. I
modified
the Row Source on the Division, Sections, and Billet
Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY
qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!
Division)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!
Division) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!
Subform.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I
have received
valuable
feedback for my questions. Unfortunately, I have
not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope
that's not
considered
unpolite). Maybe this will illustrate what I'm
trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected
in the combo boxes)
- tblBoards (resides on a mainform - values are
independent from the
cascading combos)
- tblDivisions (values are "parent" to values in
tblSections)
- tblSections (values are "parent" to values in
tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier
(Division to
Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value
of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section)
resides
- Subsubsubform: this is where 3rd tier
(BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division"
on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections"
on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3
tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom














.

1) Correct.

Given the naming convention of
- Mainform (general data)
- Subform (Division combo)
- Subsubform (Sections combo)
- Subsubsubform (BilletCode)

If changing the combobox "Division" on "Subform" (1st subform layer), the
following doesn't work for me:

Me.Subsubform.Form.Sections = Null
Me.Subsubform.Form.Sections.Requery

The names you have listed here are the names of the forms being used as
subforms, at least in the example I downloaded. However, you should be
referring to the subform control on Me. This is the control that holds the
subform you have named. In the example I downloaded, all of the subform
controls were named "subform". If this is correct on what you're working on,
change "subsubform" to "subform". The control named "subform" on the form
"Subform" holds the form names "Subsubform". The control named "subform" on
the form "Subsubform" holds the form named "Subsubsubform". This control is
what you need to refer to NOT the name of the form.

Is it safe to safe that "Me" = "Subform".

"Me" refers to the form or report that the code is running on. In this case,
yes, it appears that it will refer to the form named Subform.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

This subject "requery" on subforms is (in my opinion) a very abstract
topic.

I have tried to follow your logic but it doesn't seem to work for me.
Please be patient w/ me.

Okay, here's I guess where my trouble lies:
1. No combo boxes are being used on the "Mainform". Therefore I believe
that this entire enity (field, controls, etc.) can be taken out of the
picture, right?

2. If I open up the "Mainform" I now see all of the underlying subforms.
If I click on the 1st level of subforms (Division), I can see either a) an
symbol shaped like a fist + plus all of the "handles" where I could resize
the form... or b) I see a small gray square and when I click on it I see a
black bullet/dot in that.

If I select 2a, I see that "Subform" is listed on both the "Data" and the
"Other" tab.
If I select 2b, I only see "Subform" listed under the "Format" tab.

3. Then If I click on the actual combo box "Division" that sits on
"Subform"
I see "Division" under "Data" and "Other" tab.


I am as confident as I can be that I have followed your guidelines for
requerying one combo based on its parent combo. Still, I get errors that
indicated that "something" counldn't be found.


Given the naming convention of
- Mainform (general data)
- Subform (Division combo)
- Subsubform (Sections combo)
- Subsubsubform (BilletCode)

If changing the combobox "Division" on "Subform" (1st subform layer), the
following doesn't work for me:

Me.Subsubform.Form.Sections = Null
Me.Subsubform.Form.Sections.Requery


Is it safe to safe that "Me" = "Subform".


At this moment, I have tried all versions I could think of. I know it's
me, but I can't understand the structure of this. Please bear w/ me on
this.


Thanks again,
Tom




"Wayne Morgan" wrote in
message
...
You will need to use the syntax mention before to refer to a control on
a
subform.

Me.Subform.Form.cboCombobox = Null
Me.Subform.Form.cboCombobox.Requery

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

I have tried to requery the next level subforms... unsuccessfully
though.

Me.Requery won't do it, right? Also, I don't know how to set the
next
lower level to Null. Would you please provide me more info for that?

--
Thanks,
Tom


"Wayne Morgan" wrote in
message
...
After you make a change to the upper level combo(s), requery the
next
lower
level combo. You may also want to set the value of the next lower
level
combo to Null to clear the previous selection.

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Thanks... I'll look into the syntax (I'm away from my PC
where I stored the sample file).

Meanwhile, I realized that "refreshing" does not always
work properly.

For instance, the 1st time I e.g.
select "A", "AA", "AAA". All values were updated properly.

However, then, if I were to switch to a different
division "B" I sometimes still see "A?" or "A??" for the
division and sections, respectively.


Any additional pointers?


Tom


-----Original Message-----
1) If you refer to the query in the row source, then the
query already does
the joins for you. Basing a query on a query is legal.

2) The modifications I made were to get rid of the pop-
ups and did when I
tested it. The pop-ups happen when a query, either the
one in the row source
or the query it's based on, have parameters that it
can't "see". This would
happen if you mistype it (i.e. spelling error, syntax
error, etc) or if the
form being used as the source of the data is closed.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for your reply and the help you've given
me on this. I
also
appreciate your sharing the courtesy procedures used in
this forum.

I made the changes to the sample db, and it works
fine. Now I am
translating the same structure of "code" into my actual
db and something
doesn't seem to work out properly.

I hope you don't mind me asking a few more questions:

1. Looking at the SQL of the "qryCbo"... the code
suggests the following:
"SELECT tbl_Divisions.Division, tbl_Sections.Sections,
tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER
JOIN tbl_Sections ON
tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN
tbl_BilletCode ON tbl_Sections.SectionID =
tbl_BilletCode.SectionIDfk;"

My questions is... here it's using INNER JOINs, primary
key, foreign keys,
etc. You didn't use them in the Row Source
statement. Do I need to
modify
the RowSource to accomodate the relationships? Or
isn't this necessary?

2. Currently, I get dialog boxes popping up (beginning
on the 2nd tier
level - Sections) that prompt me to enter
the "Division" ... even though I
already selected the Division from the combo. Why is
that? What would
I
need to modify to make it work?

I hope you'll have another chance to provide me more
pointers so that I
can
achieve my goal.


Thanks so much again for your advice and help,
Tom


"Wayne Morgan"
wrote in
message
...
It is normally NOT polite to send the binary file
without someone
requesting
it first. Many folks have slow, dial-up connections
and don't want to
have
to wait for the download. Also, the potential for
viruses exists. The
file
was small and I had a non critical computer I could
open it on, so I
took
a
look. The problem is that you need to refer to the
subform control on
each
form. The subform control is a control that holds a
subform. A subform
is
not open in it own right and so the name of the
subform doesn't get you
anywhere, you have to follow the path down from the
main form. I
modified
the Row Source on the Division, Sections, and Billet
Codes combos as
follows
and it appears to do what you're asking.

SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY
qryCbo.Division;

SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!Subform.Form!
Division)) ORDER BY
qryCbo.Sections;

SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE
(((qryCbo.Division)=Forms!MainForm!SubForm.Form!
Division) And
((qryCbo.Sections)=Forms!MainForm!Subform.Form!
Subform.Form!Sections))
ORDER
BY qryCbo.BilletCode;

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Hello:

I have posted multiple threads in this forum. I
have received
valuable
feedback for my questions. Unfortunately, I have
not been able to get
my
problem with cascading combo boxes solved.

At this time, I have attached a sample file (I hope
that's not
considered
unpolite). Maybe this will illustrate what I'm
trying to achieve.

What's in the db:

Tables:
- tblDataStorage (holds values that were selected
in the combo boxes)
- tblBoards (resides on a mainform - values are
independent from the
cascading combos)
- tblDivisions (values are "parent" to values in
tblSections)
- tblSections (values are "parent" to values in
tblBilletCodes)
- tblBilletCode

Query:
- qryCbo; strings the relationships of the 3-tier
(Division to
Sections
to
BilletCodes) combo box values.

Forms:
- Mainform (contains the independent value
of "Board 1" plus the
subforms
- Subform: this is where 1st tier (Division) resides
- Subsubform: this is where 2nd tier (Section)
resides
- Subsubsubform: this is where 3rd tier
(BilletCode) resides


Here's what I need to achieve:
- When selecting a value from "Division"
on "Subform"
- then update values "Sections" accordingly
- then when selecting a value from "Sections"
on "Subsubform"
- then update values "BilletCode"
- then select appropriate BilletCode

At this time, requerying the combos between the 3
tiers don't work.
Does
anyone can provide me some help w/ that?

Thanks so much in advance,
Tom














.