Cascading Combos -- from Subfrom to SubSubform

[EEH]

I have built cascading combo on a single form before... however, I need some
help using cascading combo between different "levels" (combo1 on subform
drives data on combo2 on subsubform).

As pictures (examples) speak thousands words, I've posted a sample db at:
<http://www.savefile.com/files/5187780>

If you are ok w/ downloading this sample, please follow the steps as
outlined below:

1. by default, main form "frmIssuesOpen" opens
2. the main form has 2 subforms. The problem lies within subform labeled
"Assign Affected M&&P(s)" (actual name is "sfrmM&P")
3. This subform has 2 fields. The 1st one "Member" is locked... that's by
design. The 2nd one "Affected M&P" is the combo box that should "drive"
subsequent values in the next combo. Let's say you select "1.2" for "Affected
M&P".
4. Now, based on step #3, click on the "+" for the record. This open the
next subform ("Associated MOPMOE").
5. "Associated MOPMOE" ("sfrmMOPMOE") has only one field... the combo which
should only show those records given what you selected in #3.
6. Given the data mapping shown below, in this sample db, there are 3
"Associated MOPMOE" records for each of the 3 "Affected M&P" records.

Here's the problem... no matter which one of the 3 "Affected M&P" (1.1, 1.2,
1.3) records I select in the 1st subform, I get all 9 values in my 2nd combo.
Instead I should see only 3 ... 1.1 should show 1.1.1, 1.1.2, 1.1.3.

Any ideas how I can create the linkage between those 2 combo boxes?

Thanks,
EEH


00_tblM&P
=======
ID AffectedM_P
1 1.1
2 1.2
3 1.3


00_tblMOPMOE
==========
ID AffectedM_P MOPMOE
1 1.1 1.1.1
2 1.1 1.1.2
3 1.1 1.1.3
4 1.2 1.2.1
5 1.2 1.2.2
6 1.2 1.2.3
7 1.3 1.3.1
8 1.3 1.3.2
9 1.3 1.3.3

 

[Marshall Barton]

I have built cascading combo on a single form before... however, I need some
help using cascading combo between different "levels" (combo1 on subform
drives data on combo2 on subsubform).

As pictures (examples) speak thousands words, I've posted a sample db at:
<http://www.savefile.com/files/5187780>

If you are ok w/ downloading this sample, please follow the steps as
outlined below:

1. by default, main form "frmIssuesOpen" opens
2. the main form has 2 subforms. The problem lies within subform labeled
"Assign Affected M&&P(s)" (actual name is "sfrmM&P")
3. This subform has 2 fields. The 1st one "Member" is locked... that's by
design. The 2nd one "Affected M&P" is the combo box that should "drive"
subsequent values in the next combo. Let's say you select "1.2" for "Affected
M&P".
4. Now, based on step #3, click on the "+" for the record. This open the
next subform ("Associated MOPMOE").
5. "Associated MOPMOE" ("sfrmMOPMOE") has only one field... the combo which
should only show those records given what you selected in #3.
6. Given the data mapping shown below, in this sample db, there are 3
"Associated MOPMOE" records for each of the 3 "Affected M&P" records.

Here's the problem... no matter which one of the 3 "Affected M&P" (1.1, 1.2,
1.3) records I select in the 1st subform, I get all 9 values in my 2nd combo.
Instead I should see only 3 ... 1.1 should show 1.1.1, 1.1.2, 1.1.3.

00_tblM&P
=======
ID AffectedM_P
1 1.1
2 1.2
3 1.3


00_tblMOPMOE
==========
ID AffectedM_P MOPMOE
1 1.1 1.1.1
2 1.1 1.1.2
3 1.1 1.1.3
4 1.2 1.2.1
5 1.2 1.2.2
6 1.2 1.2.3
7 1.3 1.3.1
8 1.3 1.3.2
9 1.3 1.3.3

The seccond combo box's RowSource query needs to use a
criteria that refers back to the first combo box. Should be
something like:
Forms!frmIssuesOpen.[sfrmM&P].Form.[Affected M&P]

Don't forget that the AfterUpdate event of the Affected M&P
combo needs to requery the Associated MOPMOE combo box:
Me.[Associated MOPMOE].Form.[name of combo2].Requery

 

[EEH]

Marshall,

thanks for the reply... I appreciate it.

I'm having a bit difficulty with the REQUERY. I tried several statements.
All of them throw some errors when clicking on the 1st combo.

Here's what I've tried:
'Me.[sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].[cboAssociatedMOPMOE].Requery
Me.Parent![sfrmM&P]![sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery

Any ideas what's missing?

Tom

I have built cascading combo on a single form before... however, I need some
help using cascading combo between different "levels" (combo1 on subform
drives data on combo2 on subsubform).

As pictures (examples) speak thousands words, I've posted a sample db at:
<http://www.savefile.com/files/5187780>

If you are ok w/ downloading this sample, please follow the steps as
outlined below:

1. by default, main form "frmIssuesOpen" opens
2. the main form has 2 subforms. The problem lies within subform labeled
"Assign Affected M&&P(s)" (actual name is "sfrmM&P")
3. This subform has 2 fields. The 1st one "Member" is locked... that's by
design. The 2nd one "Affected M&P" is the combo box that should "drive"
subsequent values in the next combo. Let's say you select "1.2" for "Affected
M&P".
4. Now, based on step #3, click on the "+" for the record. This open the
next subform ("Associated MOPMOE").
5. "Associated MOPMOE" ("sfrmMOPMOE") has only one field... the combo which
should only show those records given what you selected in #3.
6. Given the data mapping shown below, in this sample db, there are 3
"Associated MOPMOE" records for each of the 3 "Affected M&P" records.

Here's the problem... no matter which one of the 3 "Affected M&P" (1.1, 1.2,
1.3) records I select in the 1st subform, I get all 9 values in my 2nd combo.
Instead I should see only 3 ... 1.1 should show 1.1.1, 1.1.2, 1.1.3.

00_tblM&P
=======
ID AffectedM_P
1 1.1
2 1.2
3 1.3


00_tblMOPMOE
==========
ID AffectedM_P MOPMOE
1 1.1 1.1.1
2 1.1 1.1.2
3 1.1 1.1.3
4 1.2 1.2.1
5 1.2 1.2.2
6 1.2 1.2.3
7 1.3 1.3.1
8 1.3 1.3.2
9 1.3 1.3.3

The seccond combo box's RowSource query needs to use a
criteria that refers back to the first combo box. Should be
something like:
Forms!frmIssuesOpen.[sfrmM&P].Form.[Affected M&P]

Don't forget that the AfterUpdate event of the Affected M&P
combo needs to requery the Associated MOPMOE combo box:
Me.[Associated MOPMOE].Form.[name of combo2].Requery

 

[Marshall Barton]

The combo box you want to requery is named
cboAssociatedMOPMOE
and it is in the subform **control** named
sfrmMOPMOE
right? If so, then, since sfrmMOPMOE is a control on the
first subform it would just be:

Me![sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery

But, apparently that didn't work when you tried it. If
you'll be more explicit than "throw some errors", maybe I
can get enough of a clue to deduce whatever is causing a
problem. At this point, all I can say is that the most
common mistake is the name of the subform **control** is not
correct. Either that or your first subform doesn't really
contain the subsubform control.
--
Marsh
MVP [MS Access]

I'm having a bit difficulty with the REQUERY. I tried several statements.
All of them throw some errors when clicking on the 1st combo.

Here's what I've tried:
'Me.[sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].[cboAssociatedMOPMOE].Requery
Me.Parent![sfrmM&P]![sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery

I have built cascading combo on a single form before... however, I need some
help using cascading combo between different "levels" (combo1 on subform
drives data on combo2 on subsubform).

As pictures (examples) speak thousands words, I've posted a sample db at:
<http://www.savefile.com/files/5187780>

If you are ok w/ downloading this sample, please follow the steps as
outlined below:

1. by default, main form "frmIssuesOpen" opens
2. the main form has 2 subforms. The problem lies within subform labeled
"Assign Affected M&&P(s)" (actual name is "sfrmM&P")
3. This subform has 2 fields. The 1st one "Member" is locked... that's by
design. The 2nd one "Affected M&P" is the combo box that should "drive"
subsequent values in the next combo. Let's say you select "1.2" for "Affected
M&P".
4. Now, based on step #3, click on the "+" for the record. This open the
next subform ("Associated MOPMOE").
5. "Associated MOPMOE" ("sfrmMOPMOE") has only one field... the combo which
should only show those records given what you selected in #3.
6. Given the data mapping shown below, in this sample db, there are 3
"Associated MOPMOE" records for each of the 3 "Affected M&P" records.

Here's the problem... no matter which one of the 3 "Affected M&P" (1.1, 1.2,
1.3) records I select in the 1st subform, I get all 9 values in my 2nd combo.
Instead I should see only 3 ... 1.1 should show 1.1.1, 1.1.2, 1.1.3.

00_tblM&P
=======
ID AffectedM_P
1 1.1
2 1.2
3 1.3


00_tblMOPMOE
==========
ID AffectedM_P MOPMOE
1 1.1 1.1.1
2 1.1 1.1.2
3 1.1 1.1.3
4 1.2 1.2.1
5 1.2 1.2.2
6 1.2 1.2.3
7 1.3 1.3.1
8 1.3 1.3.2
9 1.3 1.3.3

The seccond combo box's RowSource query needs to use a
criteria that refers back to the first combo box. Should be
something like:
Forms!frmIssuesOpen.[sfrmM&P].Form.[Affected M&P]

Don't forget that the AfterUpdate event of the Affected M&P
combo needs to requery the Associated MOPMOE combo box:
Me.[Associated MOPMOE].Form.[name of combo2].Requery

 

[Tom]

Marshall,

I read somewhere that another problem could be based on using "datasheet
view" (vs. single form). That's what I applied... before I keep wrecking
my brain, does the cascading combo approach work with forms displayed in
datasheet view?

Tom

The combo box you want to requery is named
cboAssociatedMOPMOE
and it is in the subform **control** named
sfrmMOPMOE
right? If so, then, since sfrmMOPMOE is a control on the
first subform it would just be:

Me![sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery

But, apparently that didn't work when you tried it. If
you'll be more explicit than "throw some errors", maybe I
can get enough of a clue to deduce whatever is causing a
problem. At this point, all I can say is that the most
common mistake is the name of the subform **control** is not
correct. Either that or your first subform doesn't really
contain the subsubform control.
--
Marsh
MVP [MS Access]

I'm having a bit difficulty with the REQUERY. I tried several statements.
All of them throw some errors when clicking on the 1st combo.

Here's what I've tried:
'Me.[sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].[cboAssociatedMOPMOE].Requery
Me.Parent![sfrmM&P]![sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery

EEH wrote:

I have built cascading combo on a single form before... however, I need
some
help using cascading combo between different "levels" (combo1 on
subform
drives data on combo2 on subsubform).

As pictures (examples) speak thousands words, I've posted a sample db
at:
<http://www.savefile.com/files/5187780>

If you are ok w/ downloading this sample, please follow the steps as
outlined below:

1. by default, main form "frmIssuesOpen" opens
2. the main form has 2 subforms. The problem lies within subform
labeled
"Assign Affected M&&P(s)" (actual name is "sfrmM&P")
3. This subform has 2 fields. The 1st one "Member" is locked... that's
by
design. The 2nd one "Affected M&P" is the combo box that should "drive"
subsequent values in the next combo. Let's say you select "1.2" for
"Affected
M&P".
4. Now, based on step #3, click on the "+" for the record. This open
the
next subform ("Associated MOPMOE").
5. "Associated MOPMOE" ("sfrmMOPMOE") has only one field... the combo
which
should only show those records given what you selected in #3.
6. Given the data mapping shown below, in this sample db, there are 3
"Associated MOPMOE" records for each of the 3 "Affected M&P" records.

Here's the problem... no matter which one of the 3 "Affected M&P" (1.1,
1.2,
1.3) records I select in the 1st subform, I get all 9 values in my 2nd
combo.
Instead I should see only 3 ... 1.1 should show 1.1.1, 1.1.2, 1.1.3.

00_tblM&P
=======
ID AffectedM_P
1 1.1
2 1.2
3 1.3


00_tblMOPMOE
==========
ID AffectedM_P MOPMOE
1 1.1 1.1.1
2 1.1 1.1.2
3 1.1 1.1.3
4 1.2 1.2.1
5 1.2 1.2.2
6 1.2 1.2.3
7 1.3 1.3.1
8 1.3 1.3.2
9 1.3 1.3.3


The seccond combo box's RowSource query needs to use a
criteria that refers back to the first combo box. Should be
something like:
Forms!frmIssuesOpen.[sfrmM&P].Form.[Affected M&P]

Don't forget that the AfterUpdate event of the Affected M&P
combo needs to requery the Associated MOPMOE combo box:
Me.[Associated MOPMOE].Form.[name of combo2].Requery

 

[Marshall Barton]

Although I've never used Subdatasheets I believe that if
you've set it up properly, the requery will work the same
regardless of the form view.

At this point, I am not even sure what you are "wrecking"
your brain about. I can't provide anything beyond some wild
guesses unless you provide specific details about what you
have, what you've tried and what happened.
--
Marsh
MVP [MS Access]

I read somewhere that another problem could be based on using "datasheet
view" (vs. single form). That's what I applied... before I keep wrecking
my brain, does the cascading combo approach work with forms displayed in
datasheet view?

The combo box you want to requery is named
cboAssociatedMOPMOE
and it is in the subform **control** named
sfrmMOPMOE
right? If so, then, since sfrmMOPMOE is a control on the
first subform it would just be:

Me![sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery

But, apparently that didn't work when you tried it. If
you'll be more explicit than "throw some errors", maybe I
can get enough of a clue to deduce whatever is causing a
problem. At this point, all I can say is that the most
common mistake is the name of the subform **control** is not
correct. Either that or your first subform doesn't really
contain the subsubform control.

I'm having a bit difficulty with the REQUERY. I tried several statements.
All of them throw some errors when clicking on the 1st combo.

Here's what I've tried:
'Me.[sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].Requery
'Me.Parent![sfrmM&P]![sfrmMOPMOE].[cboAssociatedMOPMOE].Requery
Me.Parent![sfrmM&P]![sfrmMOPMOE].Form.[cboAssociatedMOPMOE].Requery


:

EEH wrote:

I have built cascading combo on a single form before... however, I need
some
help using cascading combo between different "levels" (combo1 on
subform
drives data on combo2 on subsubform).

As pictures (examples) speak thousands words, I've posted a sample db
at:
<http://www.savefile.com/files/5187780>

If you are ok w/ downloading this sample, please follow the steps as
outlined below:

1. by default, main form "frmIssuesOpen" opens
2. the main form has 2 subforms. The problem lies within subform
labeled
"Assign Affected M&&P(s)" (actual name is "sfrmM&P")
3. This subform has 2 fields. The 1st one "Member" is locked... that's
by
design. The 2nd one "Affected M&P" is the combo box that should "drive"
subsequent values in the next combo. Let's say you select "1.2" for
"Affected
M&P".
4. Now, based on step #3, click on the "+" for the record. This open
the
next subform ("Associated MOPMOE").
5. "Associated MOPMOE" ("sfrmMOPMOE") has only one field... the combo
which
should only show those records given what you selected in #3.
6. Given the data mapping shown below, in this sample db, there are 3
"Associated MOPMOE" records for each of the 3 "Affected M&P" records.

Here's the problem... no matter which one of the 3 "Affected M&P" (1.1,
1.2,
1.3) records I select in the 1st subform, I get all 9 values in my 2nd
combo.
Instead I should see only 3 ... 1.1 should show 1.1.1, 1.1.2, 1.1.3.

00_tblM&P
=======
ID AffectedM_P
1 1.1
2 1.2
3 1.3


00_tblMOPMOE
==========
ID AffectedM_P MOPMOE
1 1.1 1.1.1
2 1.1 1.1.2
3 1.1 1.1.3
4 1.2 1.2.1
5 1.2 1.2.2
6 1.2 1.2.3
7 1.3 1.3.1
8 1.3 1.3.2
9 1.3 1.3.3


The seccond combo box's RowSource query needs to use a
criteria that refers back to the first combo box. Should be
something like:
Forms!frmIssuesOpen.[sfrmM&P].Form.[Affected M&P]

Don't forget that the AfterUpdate event of the Affected M&P
combo needs to requery the Associated MOPMOE combo box:
Me.[Associated MOPMOE].Form.[name of combo2].Requery