Need help with cascading combos

Wayne:

Again, I truly appreciate your help. Unfortunately, I don't get anyhere
here. I wouldn't have thought that this seems sooo complicated. (Well,
maybe not for you but for me it is).

I believe when you're referring to "the control that holds the subform",
you're talking about the value that is in the "Other tab" in the "Name"
property, right?

In your last thread, you indicated that all subform have the same control
name ("subform"). That's true for the Division and Sections, but not for
the BilletCode. Anyhow, it doesn't matter either way as long as it works.

At this moment, I think I'm more confused that I knew before I started this
thread. I'm pulling my hairs out... if I continue on this path, I'll be
bald by mid of next week.

Is there a remote chance that you provide me more specific information. I
understand that this forum is for "learning" as well. I'm just not getting
it right now. It would be truly helpful it you could provide the 2 lines of
codes with the exact naming convention of the code.

Again, I thank you for your help thus far!!!

Tom




"Wayne Morgan" wrote in message
...
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














.

Tom,

Will you zip up the database and send it with a short explanation to
?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Again, I truly appreciate your help. Unfortunately, I don't get anyhere
here. I wouldn't have thought that this seems sooo complicated. (Well,
maybe not for you but for me it is).

I believe when you're referring to "the control that holds the subform",
you're talking about the value that is in the "Other tab" in the "Name"
property, right?

In your last thread, you indicated that all subform have the same control
name ("subform"). That's true for the Division and Sections, but not for
the BilletCode. Anyhow, it doesn't matter either way as long as it works.

At this moment, I think I'm more confused that I knew before I started
this
thread. I'm pulling my hairs out... if I continue on this path, I'll be
bald by mid of next week.

Is there a remote chance that you provide me more specific information. I
understand that this forum is for "learning" as well. I'm just not
getting
it right now. It would be truly helpful it you could provide the 2 lines
of
codes with the exact naming convention of the code.

Again, I thank you for your help thus far!!!

Tom




"Wayne Morgan" wrote in
message
...
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














.

Wayne:

Thanks so much for offering this... I emailed the file to the account listed
below.

Again, I truly appreciate your help on this!!!

--
Thanks,
Tom


"Wayne Morgan" wrote in message
...
Tom,

Will you zip up the database and send it with a short explanation to
?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Again, I truly appreciate your help. Unfortunately, I don't get anyhere
here. I wouldn't have thought that this seems sooo complicated. (Well,
maybe not for you but for me it is).

I believe when you're referring to "the control that holds the subform",
you're talking about the value that is in the "Other tab" in the "Name"
property, right?

In your last thread, you indicated that all subform have the same
control
name ("subform"). That's true for the Division and Sections, but not
for
the BilletCode. Anyhow, it doesn't matter either way as long as it
works.

At this moment, I think I'm more confused that I knew before I started
this
thread. I'm pulling my hairs out... if I continue on this path, I'll be
bald by mid of next week.

Is there a remote chance that you provide me more specific information.
I
understand that this forum is for "learning" as well. I'm just not
getting
it right now. It would be truly helpful it you could provide the 2
lines
of
codes with the exact naming convention of the code.

Again, I thank you for your help thus far!!!

Tom




"Wayne Morgan" wrote in
message
...
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














.

I didn't go through everthing, but I got the comboboxes working. When you
select an item in Division, Section will be filtered. I didn't see where you
are filtering Billet yet by the Section combo. The Requery was in the form's
AfterUpdate. There was nothing to trigger it, the action was being done in
the combo box. The form doesn't update until the record is saved. The
combobox updates when you make the change it. One other thing I ran into is
that requerying the next combobox in line at this point would generate an
error unless the subdatasheet form had been displayed at least once. So, to
fix this I added a command to expand the subdatasheet before doing the
requery. The DoEvents command lets this happen before the requery line is
executed (its a timing thing, the next line was being executed before the
subdatasheet had time to expand).


--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for offering this... I emailed the file to the account
listed
below.

Again, I truly appreciate your help on this!!!

--
Thanks,
Tom


"Wayne Morgan" wrote in
message
...
Tom,

Will you zip up the database and send it with a short explanation to
?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Again, I truly appreciate your help. Unfortunately, I don't get
anyhere
here. I wouldn't have thought that this seems sooo complicated.
(Well,
maybe not for you but for me it is).

I believe when you're referring to "the control that holds the
subform",
you're talking about the value that is in the "Other tab" in the
"Name"
property, right?

In your last thread, you indicated that all subform have the same
control
name ("subform"). That's true for the Division and Sections, but not
for
the BilletCode. Anyhow, it doesn't matter either way as long as it
works.

At this moment, I think I'm more confused that I knew before I started
this
thread. I'm pulling my hairs out... if I continue on this path, I'll
be
bald by mid of next week.

Is there a remote chance that you provide me more specific
information.
I
understand that this forum is for "learning" as well. I'm just not
getting
it right now. It would be truly helpful it you could provide the 2
lines
of
codes with the exact naming convention of the code.

Again, I thank you for your help thus far!!!

Tom




"Wayne Morgan" wrote in
message
...
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














.

Wayne:

First of all... THANKS!!!

I'm not sure if I'm entirely following your comments below. This morning I
fixed the "dependency logic" between SectionCodes and BilletCodes (e.g. if
"AA" then "AAA", "AAB", or "AAC", etc.).

I guess -- based on what you have worked on -- the main problem still
persists.

That problem is the following:
- Currently, there's only "A" selected for a board.
- If you add "B", you get the appropriate SectionCode (BA, BB, BC).
- If you then select "BA", you'll get "BAA, BAB, BAC"... so far so good...

Problem begins he
- After having selected "BA", if I then add "BB" in the SectionCode, I still
see the previous values from BA (BAA, BAB, BAC) instead of "BBA, BBB, BBC"
- The same holds up for selecting "C" after having entered "B". I see "BA,
BB, BC" instead of "CA, CB, CC".

Is there a chance to requery the SectionCode and BilletCode for each time
when changing a different value. I cannot always closes the form and
reopen it after having made a single selection.


I'll email you the updated version (your AfterUpdate code) w/ my combined
change of "SectionCode" drives "BilletCode".


I hope you won't mind having another look on this.


Thanks!!!

Tom





"Wayne Morgan" wrote in message
...
I didn't go through everthing, but I got the comboboxes working. When you
select an item in Division, Section will be filtered. I didn't see where
you
are filtering Billet yet by the Section combo. The Requery was in the
form's
AfterUpdate. There was nothing to trigger it, the action was being done in
the combo box. The form doesn't update until the record is saved. The
combobox updates when you make the change it. One other thing I ran into
is
that requerying the next combobox in line at this point would generate an
error unless the subdatasheet form had been displayed at least once. So,
to
fix this I added a command to expand the subdatasheet before doing the
requery. The DoEvents command lets this happen before the requery line is
executed (its a timing thing, the next line was being executed before the
subdatasheet had time to expand).


--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Thanks so much for offering this... I emailed the file to the account
listed
below.

Again, I truly appreciate your help on this!!!

--
Thanks,
Tom


"Wayne Morgan" wrote in
message
...
Tom,

Will you zip up the database and send it with a short explanation to
?

--
Wayne Morgan
MS Access MVP


"Tom" wrote in message
...
Wayne:

Again, I truly appreciate your help. Unfortunately, I don't get
anyhere
here. I wouldn't have thought that this seems sooo complicated.
(Well,
maybe not for you but for me it is).

I believe when you're referring to "the control that holds the
subform",
you're talking about the value that is in the "Other tab" in the
"Name"
property, right?

In your last thread, you indicated that all subform have the same
control
name ("subform"). That's true for the Division and Sections, but
not
for
the BilletCode. Anyhow, it doesn't matter either way as long as it
works.

At this moment, I think I'm more confused that I knew before I
started
this
thread. I'm pulling my hairs out... if I continue on this path,
I'll
be
bald by mid of next week.

Is there a remote chance that you provide me more specific
information.
I
understand that this forum is for "learning" as well. I'm just not
getting
it right now. It would be truly helpful it you could provide the 2
lines
of
codes with the exact naming convention of the code.

Again, I thank you for your help thus far!!!

Tom




"Wayne Morgan" wrote in
message
...
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














.

Tom,

I don't mind having another look, but I've checked my email and haven't seen
anything. If you sent it, it got lost.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

First of all... THANKS!!!

I'm not sure if I'm entirely following your comments below. This morning
I
fixed the "dependency logic" between SectionCodes and BilletCodes (e.g. if
"AA" then "AAA", "AAB", or "AAC", etc.).

I guess -- based on what you have worked on -- the main problem still
persists.

That problem is the following:
- Currently, there's only "A" selected for a board.
- If you add "B", you get the appropriate SectionCode (BA, BB, BC).
- If you then select "BA", you'll get "BAA, BAB, BAC"... so far so good...

Problem begins he
- After having selected "BA", if I then add "BB" in the SectionCode, I
still
see the previous values from BA (BAA, BAB, BAC) instead of "BBA, BBB, BBC"
- The same holds up for selecting "C" after having entered "B". I see
"BA,
BB, BC" instead of "CA, CB, CC".

Is there a chance to requery the SectionCode and BilletCode for each time
when changing a different value. I cannot always closes the form and
reopen it after having made a single selection.


I'll email you the updated version (your AfterUpdate code) w/ my combined
change of "SectionCode" drives "BilletCode".


I hope you won't mind having another look on this.


Thanks!!!

Tom

Wayne:

I used a webmail account for sending the attachment. You aren't the 1st
person telling me that the attachment didn't get send. As a result, I have
attached the document in this thread. I understand it's not really polite
doing this but since it's a small file, I thought it might be okay.

But first let me "say" that I truly appreciate your help in this matter.
This thread has been ongoing for some time; nevertheless, you always
responded and offered your help. That's simply awesome.

Okay, here's what still not working properly:
1. On the level "Divsion" and "Section" I get the error "2455" when adding
duplicates. In one of the earliest version, I included a "delete record"
command that was called when a user attempted to enter the same value twice.
I think this error now is called since "it can not requery the child combo
box if the value of the parent combo box does not exist".... but then,
that's only what I'm thinking... maybe I'm wrong. Since there is no
dependency below the BilletCode combo box, the "delete function" works fine
on the lowest level. At any rate, I need to overcome the error 2455 on the
Division and Section level. Users will attempt to enter duplicates and I
simply want the db take care of this problem.

2. Is there a way to NOT expand the subdatasheet but requery the values at
the same time? When entering lots of data on different levels, all expanded
levels could look confusing to some users. At the same time, I must ensure
that the requeries are done immediately when switching values. Also, I
noticed that sometimes the requery did not always update when moving from a
lower level data entry to a higher level entry. What I mean by that is that
when having selected e.g. "BB" on the Section Level, then entering A
(Division level) will not always show "AA, AB, AC" in the subordinate combo
boxes. Sometimes it still shows "BA, BB, BC". I didn't see a straight
pattern there though.

Hopefully, this information was sufficient to provide you an idea as to what
the remaining issues are. Again, thanks for all of your help in this
matter.

Tom







"Wayne Morgan" wrote in message
...
Tom,

I don't mind having another look, but I've checked my email and haven't
seen
anything. If you sent it, it got lost.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

First of all... THANKS!!!

I'm not sure if I'm entirely following your comments below. This
morning
I
fixed the "dependency logic" between SectionCodes and BilletCodes (e.g.
if
"AA" then "AAA", "AAB", or "AAC", etc.).

I guess -- based on what you have worked on -- the main problem still
persists.

That problem is the following:
- Currently, there's only "A" selected for a board.
- If you add "B", you get the appropriate SectionCode (BA, BB, BC).
- If you then select "BA", you'll get "BAA, BAB, BAC"... so far so
good...

Problem begins he
- After having selected "BA", if I then add "BB" in the SectionCode, I
still
see the previous values from BA (BAA, BAB, BAC) instead of "BBA, BBB,
BBC"
- The same holds up for selecting "C" after having entered "B". I see
"BA,
BB, BC" instead of "CA, CB, CC".

Is there a chance to requery the SectionCode and BilletCode for each
time
when changing a different value. I cannot always closes the form and
reopen it after having made a single selection.


I'll email you the updated version (your AfterUpdate code) w/ my
combined
change of "SectionCode" drives "BilletCode".


I hope you won't mind having another look on this.


Thanks!!!

Tom

1. You were on the right track with the reason for the 2455 error, I added
an error handler to take care of this.

2. I tried it without expanding the subdatasheet first, but it wouldn't see
the combo on the subform until it had been expanded at least one time. Also,
it appears that if there is data in the subform it expands anyway. I'll see
if it can be expanded and collapsed again and still work, although to make
the next selection it will need to be expanded.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

I used a webmail account for sending the attachment. You aren't the 1st
person telling me that the attachment didn't get send. As a result, I
have
attached the document in this thread. I understand it's not really polite
doing this but since it's a small file, I thought it might be okay.

But first let me "say" that I truly appreciate your help in this matter.
This thread has been ongoing for some time; nevertheless, you always
responded and offered your help. That's simply awesome.

Okay, here's what still not working properly:
1. On the level "Divsion" and "Section" I get the error "2455" when adding
duplicates. In one of the earliest version, I included a "delete record"
command that was called when a user attempted to enter the same value
twice.
I think this error now is called since "it can not requery the child combo
box if the value of the parent combo box does not exist".... but then,
that's only what I'm thinking... maybe I'm wrong. Since there is no
dependency below the BilletCode combo box, the "delete function" works
fine
on the lowest level. At any rate, I need to overcome the error 2455 on
the
Division and Section level. Users will attempt to enter duplicates and I
simply want the db take care of this problem.

2. Is there a way to NOT expand the subdatasheet but requery the values at
the same time? When entering lots of data on different levels, all
expanded
levels could look confusing to some users. At the same time, I must
ensure
that the requeries are done immediately when switching values. Also, I
noticed that sometimes the requery did not always update when moving from
a
lower level data entry to a higher level entry. What I mean by that is
that
when having selected e.g. "BB" on the Section Level, then entering A
(Division level) will not always show "AA, AB, AC" in the subordinate
combo
boxes. Sometimes it still shows "BA, BB, BC". I didn't see a straight
pattern there though.

Hopefully, this information was sufficient to provide you an idea as to
what
the remaining issues are. Again, thanks for all of your help in this
matter.

Tom

Wayne:

Thanks, I send you an email w/ a final question. Bummer, I now realize
that I should have posted this question in here... unfortunately, the
webbrowser I'm using does NOT have a SENT box.

Hence, would you please respond to the email one more time.


Thanks,
Tom




"Wayne Morgan" wrote in message
...
1. You were on the right track with the reason for the 2455 error, I added
an error handler to take care of this.

2. I tried it without expanding the subdatasheet first, but it wouldn't
see
the combo on the subform until it had been expanded at least one time.
Also,
it appears that if there is data in the subform it expands anyway. I'll
see
if it can be expanded and collapsed again and still work, although to make
the next selection it will need to be expanded.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

I used a webmail account for sending the attachment. You aren't the 1st
person telling me that the attachment didn't get send. As a result, I
have
attached the document in this thread. I understand it's not really
polite
doing this but since it's a small file, I thought it might be okay.

But first let me "say" that I truly appreciate your help in this matter.
This thread has been ongoing for some time; nevertheless, you always
responded and offered your help. That's simply awesome.

Okay, here's what still not working properly:
1. On the level "Divsion" and "Section" I get the error "2455" when
adding
duplicates. In one of the earliest version, I included a "delete
record"
command that was called when a user attempted to enter the same value
twice.
I think this error now is called since "it can not requery the child
combo
box if the value of the parent combo box does not exist".... but then,
that's only what I'm thinking... maybe I'm wrong. Since there is no
dependency below the BilletCode combo box, the "delete function" works
fine
on the lowest level. At any rate, I need to overcome the error 2455 on
the
Division and Section level. Users will attempt to enter duplicates and
I
simply want the db take care of this problem.

2. Is there a way to NOT expand the subdatasheet but requery the values
at
the same time? When entering lots of data on different levels, all
expanded
levels could look confusing to some users. At the same time, I must
ensure
that the requeries are done immediately when switching values. Also, I
noticed that sometimes the requery did not always update when moving
from
a
lower level data entry to a higher level entry. What I mean by that is
that
when having selected e.g. "BB" on the Section Level, then entering A
(Division level) will not always show "AA, AB, AC" in the subordinate
combo
boxes. Sometimes it still shows "BA, BB, BC". I didn't see a
straight
pattern there though.

Hopefully, this information was sufficient to provide you an idea as to
what
the remaining issues are. Again, thanks for all of your help in this
matter.

Tom

Wayne:

Your help has been superb in this matter. I received your updated combo
box db... it works out great now.

As a mentioned to you in the email, you truly deserve the status "MVP"... no
doubt about it!!!

Again, thanks so much!!

--
Thanks,
Tom


"Tom" wrote in message
...
Wayne:

Thanks, I send you an email w/ a final question. Bummer, I now realize
that I should have posted this question in here... unfortunately, the
webbrowser I'm using does NOT have a SENT box.

Hence, would you please respond to the email one more time.


Thanks,
Tom




"Wayne Morgan" wrote in
message
...
1. You were on the right track with the reason for the 2455 error, I
added
an error handler to take care of this.

2. I tried it without expanding the subdatasheet first, but it wouldn't
see
the combo on the subform until it had been expanded at least one time.
Also,
it appears that if there is data in the subform it expands anyway. I'll
see
if it can be expanded and collapsed again and still work, although to
make
the next selection it will need to be expanded.

--
Wayne Morgan
Microsoft Access MVP


"Tom" wrote in message
...
Wayne:

I used a webmail account for sending the attachment. You aren't the
1st
person telling me that the attachment didn't get send. As a result,
I
have
attached the document in this thread. I understand it's not really
polite
doing this but since it's a small file, I thought it might be okay.

But first let me "say" that I truly appreciate your help in this
matter.
This thread has been ongoing for some time; nevertheless, you always
responded and offered your help. That's simply awesome.

Okay, here's what still not working properly:
1. On the level "Divsion" and "Section" I get the error "2455" when
adding
duplicates. In one of the earliest version, I included a "delete
record"
command that was called when a user attempted to enter the same value
twice.
I think this error now is called since "it can not requery the child
combo
box if the value of the parent combo box does not exist".... but then,
that's only what I'm thinking... maybe I'm wrong. Since there is no
dependency below the BilletCode combo box, the "delete function" works
fine
on the lowest level. At any rate, I need to overcome the error 2455
on
the
Division and Section level. Users will attempt to enter duplicates
and
I
simply want the db take care of this problem.

2. Is there a way to NOT expand the subdatasheet but requery the
values
at
the same time? When entering lots of data on different levels, all
expanded
levels could look confusing to some users. At the same time, I must
ensure
that the requeries are done immediately when switching values. Also,
I
noticed that sometimes the requery did not always update when moving
from
a
lower level data entry to a higher level entry. What I mean by that
is
that
when having selected e.g. "BB" on the Section Level, then entering A
(Division level) will not always show "AA, AB, AC" in the subordinate
combo
boxes. Sometimes it still shows "BA, BB, BC". I didn't see a
straight
pattern there though.

Hopefully, this information was sufficient to provide you an idea as
to
what
the remaining issues are. Again, thanks for all of your help in this
matter.

Tom