Data Dependencies between Combo Boxes

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I need to create data dependencies between combo boxes.

Let's say I have the following table hierarchy:

tblDivision (parent of tblSection)
tblSection (parent of tblBilletCode)


TABLES:

tblDivision contains the following field(s) and data:
Division
A
B
C


tblSections contains the following field(s) and data:
Division Sections
A AA
A AB
B BA
B BB
C CA
C CB



tblBilletCodes contains the following field(s) and data:
Division Sections BilletCode
A AA AAA
A AA AAB
A AA AAC
A AA AAD
A AA AAE
A AB ABA
A AB ABB
A AB ABC
B BA BAA
B BA BAB
B BA BAC
B BB BBA
C CA CAA
C CA CAB
C CB CBA
C CB CBB
C CB CBC



Currently, I have the 3 combos (Division, Sections, BilletCodes) on a single form.


QUERIES:
For queries, I use the following:

1. qryDivision:
SELECT tbl_NatoDivisions.Division FROM tbl_NatoDivisions ORDER BY tbl_NatoDivisions.Division;


2. qrySections:
SELECT tbl_NatoDivisionsSections.Sections FROM tbl_NatoDivisionsSections
WHERE (((tbl_NatoDivisionsSections.Division)=[Forms]![frmUpdateInfo]![Division]))
ORDER BY tbl_NatoDivisionsSections.Sections;


3. qryBilletCode:
SELECT tbl_NatoDivisionsSectionsBillets.BilletCode
FROM tbl_NatoDivisionsSectionsBillets
GROUP BY tbl_NatoDivisionsSectionsBillets.BilletCode, tbl_NatoDivisionsSectionsBillets.Sections
HAVING (((tbl_NatoDivisionsSectionsBillets.Sections)=[Forms]![frmUpdateInfo]![Sections]))
ORDER BY tbl_NatoDivisionsSectionsBillets.BilletCode;


4. qryUpdateInfo
SELECT tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections, tbl_UpdateInfo.BilletCode
FROM tbl_UpdateInfo
ORDER BY tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections DESC;



FORMS:

Again, in the form frmUpdatInfo, I have placed the 3 combo boxes.

For both the Division and Section combo box, I placed the following code in the AfterChange event handler:


&&&&&&&&&&&&&&

Private Sub Division_Change()
Dim Division As ComboBox, Sections As ComboBox, BilletCode As ComboBox
Set Division = Forms![frmUpdateInfo].[Division]
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

Sections.Requery
Me.Sections = Me.Sections.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

Private Sub Sections_Change()
Dim Sections As ComboBox, BilletCode As ComboBox
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

&&&&&&&&&&&&&&




Okay, here now is the problem:

1. In a different application, I have am using a main form (Division) plus a subform (Section) plus a sub-subform (BilletCode). At this time (maybe because of the multipe subform layers) this approach does NOT work any longer.

2. Overall, this approach (even on a single form) seems very complicated.


So, my questions are... first, is there is an easier and more efficient way to show only specific values in a combo box (based on the superior combo box values)? If yes, could someone please
provide me some detailed pointers how to re-structure the data dependencies?

Second, if my approach seems to be reasonable, what may I have done wrong with the translation from single form to multiple sub forms?


Any help is truly appreciated!!!


Thanks in advance,
Tom
 
See The ACCESS Web for one way to make filter one combo box based on another
combo box's selection:

http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>

I need to create data dependencies between combo boxes.

Let's say I have the following table hierarchy:

tblDivision (parent of tblSection)
tblSection (parent of tblBilletCode)


TABLES:

tblDivision contains the following field(s) and data:
Division
A
B
C


tblSections contains the following field(s) and data:
Division Sections
A AA
A AB
B BA
B BB
C CA
C CB



tblBilletCodes contains the following field(s) and data:
Division Sections BilletCode
A AA AAA
A AA AAB
A AA AAC
A AA AAD
A AA AAE
A AB ABA
A AB ABB
A AB ABC
B BA BAA
B BA BAB
B BA BAC
B BB BBA
C CA CAA
C CA CAB
C CB CBA
C CB CBB
C CB CBC



Currently, I have the 3 combos (Division, Sections, BilletCodes) on a single
form.


QUERIES:
For queries, I use the following:

1. qryDivision:
SELECT tbl_NatoDivisions.Division FROM tbl_NatoDivisions ORDER BY
tbl_NatoDivisions.Division;


2. qrySections:
SELECT tbl_NatoDivisionsSections.Sections FROM tbl_NatoDivisionsSections
WHERE
(((tbl_NatoDivisionsSections.Division)=[Forms]![frmUpdateInfo]![Division]))
ORDER BY tbl_NatoDivisionsSections.Sections;


3. qryBilletCode:
SELECT tbl_NatoDivisionsSectionsBillets.BilletCode
FROM tbl_NatoDivisionsSectionsBillets
GROUP BY tbl_NatoDivisionsSectionsBillets.BilletCode,
tbl_NatoDivisionsSectionsBillets.Sections
HAVING
(((tbl_NatoDivisionsSectionsBillets.Sections)=[Forms]![frmUpdateInfo]![Secti
ons]))
ORDER BY tbl_NatoDivisionsSectionsBillets.BilletCode;


4. qryUpdateInfo
SELECT tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections,
tbl_UpdateInfo.BilletCode
FROM tbl_UpdateInfo
ORDER BY tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections DESC;



FORMS:

Again, in the form frmUpdatInfo, I have placed the 3 combo boxes.

For both the Division and Section combo box, I placed the following code in
the AfterChange event handler:


&&&&&&&&&&&&&&

Private Sub Division_Change()
Dim Division As ComboBox, Sections As ComboBox, BilletCode As ComboBox
Set Division = Forms![frmUpdateInfo].[Division]
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

Sections.Requery
Me.Sections = Me.Sections.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

Private Sub Sections_Change()
Dim Sections As ComboBox, BilletCode As ComboBox
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

&&&&&&&&&&&&&&




Okay, here now is the problem:

1. In a different application, I have am using a main form (Division) plus a
subform (Section) plus a sub-subform (BilletCode). At this time (maybe
because of the multipe subform layers) this approach does NOT work any
longer.

2. Overall, this approach (even on a single form) seems very complicated.


So, my questions are... first, is there is an easier and more efficient way
to show only specific values in a combo box (based on the superior combo box
values)? If yes, could someone please
provide me some detailed pointers how to re-structure the data dependencies?

Second, if my approach seems to be reasonable, what may I have done wrong
with the translation from single form to multiple sub forms?


Any help is truly appreciated!!!


Thanks in advance,
Tom
 
Ken:

I'll give it a try.... looks so much easier then my way of doing it.
Hopefully it works w/ subforms, too.

--
Thanks,
Tom


Ken Snell said:
See The ACCESS Web for one way to make filter one combo box based on another
combo box's selection:

http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>

I need to create data dependencies between combo boxes.

Let's say I have the following table hierarchy:

tblDivision (parent of tblSection)
tblSection (parent of tblBilletCode)


TABLES:

tblDivision contains the following field(s) and data:
Division
A
B
C


tblSections contains the following field(s) and data:
Division Sections
A AA
A AB
B BA
B BB
C CA
C CB



tblBilletCodes contains the following field(s) and data:
Division Sections BilletCode
A AA AAA
A AA AAB
A AA AAC
A AA AAD
A AA AAE
A AB ABA
A AB ABB
A AB ABC
B BA BAA
B BA BAB
B BA BAC
B BB BBA
C CA CAA
C CA CAB
C CB CBA
C CB CBB
C CB CBC



Currently, I have the 3 combos (Division, Sections, BilletCodes) on a single
form.


QUERIES:
For queries, I use the following:

1. qryDivision:
SELECT tbl_NatoDivisions.Division FROM tbl_NatoDivisions ORDER BY
tbl_NatoDivisions.Division;


2. qrySections:
SELECT tbl_NatoDivisionsSections.Sections FROM tbl_NatoDivisionsSections
WHERE
(((tbl_NatoDivisionsSections.Division)=[Forms]![frmUpdateInfo]![Division]))

ORDER BY tbl_NatoDivisionsSections.Sections;


3. qryBilletCode:
SELECT tbl_NatoDivisionsSectionsBillets.BilletCode
FROM tbl_NatoDivisionsSectionsBillets
GROUP BY tbl_NatoDivisionsSectionsBillets.BilletCode,
tbl_NatoDivisionsSectionsBillets.Sections
HAVING
(((tbl_NatoDivisionsSectionsBillets.Sections)=[Forms]![frmUpdateInfo]![Secti
ons]))
ORDER BY tbl_NatoDivisionsSectionsBillets.BilletCode;


4. qryUpdateInfo
SELECT tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections,
tbl_UpdateInfo.BilletCode
FROM tbl_UpdateInfo
ORDER BY tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections DESC;



FORMS:

Again, in the form frmUpdatInfo, I have placed the 3 combo boxes.

For both the Division and Section combo box, I placed the following code in
the AfterChange event handler:


&&&&&&&&&&&&&&

Private Sub Division_Change()
Dim Division As ComboBox, Sections As ComboBox, BilletCode As ComboBox
Set Division = Forms![frmUpdateInfo].[Division]
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

Sections.Requery
Me.Sections = Me.Sections.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

Private Sub Sections_Change()
Dim Sections As ComboBox, BilletCode As ComboBox
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

&&&&&&&&&&&&&&




Okay, here now is the problem:

1. In a different application, I have am using a main form (Division) plus a
subform (Section) plus a sub-subform (BilletCode). At this time (maybe
because of the multipe subform layers) this approach does NOT work any
longer.

2. Overall, this approach (even on a single form) seems very complicated.


So, my questions are... first, is there is an easier and more efficient way
to show only specific values in a combo box (based on the superior combo box
values)? If yes, could someone please
provide me some detailed pointers how to re-structure the data dependencies?

Second, if my approach seems to be reasonable, what may I have done wrong
with the translation from single form to multiple sub forms?


Any help is truly appreciated!!!


Thanks in advance,
Tom
Click to expand...
 
Based on the information listed on the solution you provided, I have attempted
to replicate the scenario. Although, I get this to work, the results are not
what I had expected. Let me provide the background information first and ask
my questions at the end of this thread (below the ****s).


Background Info:

TABLE
=====

1 Table: "tblSourceData"
3 Fields: Division, Sections, BilletCode


Example Data in tblSourceData
=============================

Division Sections BilletCode
A AA AAA
A AA AAB
A AB ABA
A AB ABB
B BA BAA
B BA BAB
B BB BBA
B BB BBB


FORM
====
1 Form: "frmSourceData"
3 Combo Boxes: Division, Sections, BilletCode



Combobox "Division":
====================
Control Source = Division
Row Source Type = Field List
Row Source = tblSourceData

After Update event is the following:

Private Sub Division_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Division
strSQL = strSQL & " from tblSourceData"
Me!Sections.RowSourceType = "Table/Query"
Me!Sections.RowSource = strSQL
End Sub


Combobox "Sections":
====================
Control Source = Sections
Row Source Type = not applicable
Row Source = not applicable

After Update event is the following:

Private Sub Sections_AfterUpdate()
Me!Sections.Requery
End Sub


Others:
=======

I did not know what to do w/ the following:

"Select Division, Sections from tblSourceData Where Division= Forms!frmSourceData!Division;"



**** Questions *****

Currently, I see "Division, Sections, BilletCodes" in the combobox
"Division".

However, based on the example data, I wanted to see the following:
- when selecting value "A" in combobox "Division",
- then populate values "AA, AB" into combobox "Section"
- then when selecting value "AA" in combobox "Section"
- then populated value "AAA, AAB" into combobox "BilletCode"
- same for the others... e.g. select B, then see only BA & BB,
select BA, then see only BAA & BAB


So my questions are:
1. Is there a chance to modify the scenario to accomodate this process?
2. What do I need to do w/ the "Select Division, Sections from tblSourceData Where
Division= Forms!frmSourceData!Division;"
3. If this is possible, how do I modify the 2nd level (Sections) to accomodate
the 3rd level (BilletCode)?



Thanks so much in advance,
Tom





Tom said:
Ken:

I'll give it a try.... looks so much easier then my way of doing it.
Hopefully it works w/ subforms, too.

--
Thanks,
Tom


Ken Snell said:
See The ACCESS Web for one way to make filter one combo box based on another
combo box's selection:

http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>

I need to create data dependencies between combo boxes.

Let's say I have the following table hierarchy:

tblDivision (parent of tblSection)
tblSection (parent of tblBilletCode)


TABLES:

tblDivision contains the following field(s) and data:
Division
A
B
C


tblSections contains the following field(s) and data:
Division Sections
A AA
A AB
B BA
B BB
C CA
C CB



tblBilletCodes contains the following field(s) and data:
Division Sections BilletCode
A AA AAA
A AA AAB
A AA AAC
A AA AAD
A AA AAE
A AB ABA
A AB ABB
A AB ABC
B BA BAA
B BA BAB
B BA BAC
B BB BBA
C CA CAA
C CA CAB
C CB CBA
C CB CBB
C CB CBC



Currently, I have the 3 combos (Division, Sections, BilletCodes) on a single
form.


QUERIES:
For queries, I use the following:

1. qryDivision:
SELECT tbl_NatoDivisions.Division FROM tbl_NatoDivisions ORDER BY
tbl_NatoDivisions.Division;


2. qrySections:
SELECT tbl_NatoDivisionsSections.Sections FROM tbl_NatoDivisionsSections
WHERE
(((tbl_NatoDivisionsSections.Division)=[Forms]![frmUpdateInfo]![Division]))

ORDER BY tbl_NatoDivisionsSections.Sections;


3. qryBilletCode:
SELECT tbl_NatoDivisionsSectionsBillets.BilletCode
FROM tbl_NatoDivisionsSectionsBillets
GROUP BY tbl_NatoDivisionsSectionsBillets.BilletCode,
tbl_NatoDivisionsSectionsBillets.Sections
HAVING
(((tbl_NatoDivisionsSectionsBillets.Sections)=[Forms]![frmUpdateInfo]![Secti
ons]))
ORDER BY tbl_NatoDivisionsSectionsBillets.BilletCode;


4. qryUpdateInfo
SELECT tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections,
tbl_UpdateInfo.BilletCode
FROM tbl_UpdateInfo
ORDER BY tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections DESC;



FORMS:

Again, in the form frmUpdatInfo, I have placed the 3 combo boxes.

For both the Division and Section combo box, I placed the following code in
the AfterChange event handler:


&&&&&&&&&&&&&&

Private Sub Division_Change()
Dim Division As ComboBox, Sections As ComboBox, BilletCode As ComboBox
Set Division = Forms![frmUpdateInfo].[Division]
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

Sections.Requery
Me.Sections = Me.Sections.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

Private Sub Sections_Change()
Dim Sections As ComboBox, BilletCode As ComboBox
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

&&&&&&&&&&&&&&




Okay, here now is the problem:

1. In a different application, I have am using a main form (Division) plus a
subform (Section) plus a sub-subform (BilletCode). At this time (maybe
because of the multipe subform layers) this approach does NOT work any
longer.

2. Overall, this approach (even on a single form) seems very complicated.


So, my questions are... first, is there is an easier and more efficient way
to show only specific values in a combo box (based on the superior combo box
values)? If yes, could someone please
provide me some detailed pointers how to re-structure the data dependencies?

Second, if my approach seems to be reasonable, what may I have done wrong
with the translation from single form to multiple sub forms?


Any help is truly appreciated!!!


Thanks in advance,
Tom
Click to expand...
Click to expand...
 
The row source query for the third combo box will need to have two parts to
the criterion statement. You need to repeat the criterion of the second
combo box and add the criterion for the value in the second box.

The row source query for the second combo box should be something like this:

"Select Sections from tblSourceData Where Division=
Forms!frmSourceData!Division;"


The row source query for the third combo box should be something like this:

"Select Billets from tblSourceData Where Division=
Forms!frmSourceData!Division And Sections = Forms!frmSourceData!Sections;"


Change the After Update code for the Divisions combo box to this:

Private Sub Divisions_AfterUpdate()
Me.Sections.Requery
End Sub

Make the After Update code for the Sections combo box to be this:

Private Sub Sections_AfterUpdate()
Me.Billets.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


Based on the information listed on the solution you provided, I have
attempted
to replicate the scenario. Although, I get this to work, the results are
not
what I had expected. Let me provide the background information first and
ask
my questions at the end of this thread (below the ****s).


Background Info:

TABLE
=====

1 Table: "tblSourceData"
3 Fields: Division, Sections, BilletCode


Example Data in tblSourceData
=============================

Division Sections BilletCode
A AA AAA
A AA AAB
A AB ABA
A AB ABB
B BA BAA
B BA BAB
B BB BBA
B BB BBB


FORM
====
1 Form: "frmSourceData"
3 Combo Boxes: Division, Sections, BilletCode



Combobox "Division":
====================
Control Source = Division
Row Source Type = Field List
Row Source = tblSourceData

After Update event is the following:

Private Sub Division_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Division
strSQL = strSQL & " from tblSourceData"
Me!Sections.RowSourceType = "Table/Query"
Me!Sections.RowSource = strSQL
End Sub


Combobox "Sections":
====================
Control Source = Sections
Row Source Type = not applicable
Row Source = not applicable

After Update event is the following:

Private Sub Sections_AfterUpdate()
Me!Sections.Requery
End Sub


Others:
=======

I did not know what to do w/ the following:

"Select Division, Sections from tblSourceData Where Division=
Forms!frmSourceData!Division;"



**** Questions *****

Currently, I see "Division, Sections, BilletCodes" in the combobox
"Division".

However, based on the example data, I wanted to see the following:
- when selecting value "A" in combobox "Division",
- then populate values "AA, AB" into combobox "Section"
- then when selecting value "AA" in combobox "Section"
- then populated value "AAA, AAB" into combobox "BilletCode"
- same for the others... e.g. select B, then see only BA & BB,
select BA, then see only BAA & BAB


So my questions are:
1. Is there a chance to modify the scenario to accomodate this process?
2. What do I need to do w/ the "Select Division, Sections from tblSourceData
Where
Division= Forms!frmSourceData!Division;"
3. If this is possible, how do I modify the 2nd level (Sections) to
accomodate
the 3rd level (BilletCode)?



Thanks so much in advance,
Tom





Tom said:
Ken:

I'll give it a try.... looks so much easier then my way of doing it.
Hopefully it works w/ subforms, too.

--
Thanks,
Tom


Ken Snell said:
See The ACCESS Web for one way to make filter one combo box based on another
combo box's selection:

http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>

I need to create data dependencies between combo boxes.

Let's say I have the following table hierarchy:

tblDivision (parent of tblSection)
tblSection (parent of tblBilletCode)


TABLES:

tblDivision contains the following field(s) and data:
Division
A
B
C


tblSections contains the following field(s) and data:
Division Sections
A AA
A AB
B BA
B BB
C CA
C CB



tblBilletCodes contains the following field(s) and data:
Division Sections BilletCode
A AA AAA
A AA AAB
A AA AAC
A AA AAD
A AA AAE
A AB ABA
A AB ABB
A AB ABC
B BA BAA
B BA BAB
B BA BAC
B BB BBA
C CA CAA
C CA CAB
C CB CBA
C CB CBB
C CB CBC



Currently, I have the 3 combos (Division, Sections, BilletCodes) on a single
form.


QUERIES:
For queries, I use the following:

1. qryDivision:
SELECT tbl_NatoDivisions.Division FROM tbl_NatoDivisions ORDER BY
tbl_NatoDivisions.Division;


2. qrySections:
SELECT tbl_NatoDivisionsSections.Sections FROM tbl_NatoDivisionsSections
WHERE
Click to expand...
(((tbl_NatoDivisionsSections.Division)=[Forms]![frmUpdateInfo]![Division]))
ORDER BY tbl_NatoDivisionsSections.Sections;


3. qryBilletCode:
SELECT tbl_NatoDivisionsSectionsBillets.BilletCode
FROM tbl_NatoDivisionsSectionsBillets
GROUP BY tbl_NatoDivisionsSectionsBillets.BilletCode,
tbl_NatoDivisionsSectionsBillets.Sections
HAVING
Click to expand...
(((tbl_NatoDivisionsSectionsBillets.Sections)=[Forms]![frmUpdateInfo]![Secti
ons]))
ORDER BY tbl_NatoDivisionsSectionsBillets.BilletCode;


4. qryUpdateInfo
SELECT tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections,
tbl_UpdateInfo.BilletCode
FROM tbl_UpdateInfo
ORDER BY tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections DESC;



FORMS:

Again, in the form frmUpdatInfo, I have placed the 3 combo boxes.

For both the Division and Section combo box, I placed the following code in
the AfterChange event handler:


&&&&&&&&&&&&&&

Private Sub Division_Change()
Dim Division As ComboBox, Sections As ComboBox, BilletCode As ComboBox
Set Division = Forms![frmUpdateInfo].[Division]
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

Sections.Requery
Me.Sections = Me.Sections.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

Private Sub Sections_Change()
Dim Sections As ComboBox, BilletCode As ComboBox
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

&&&&&&&&&&&&&&




Okay, here now is the problem:

1. In a different application, I have am using a main form (Division)
Click to expand...
plus
a
subform (Section) plus a sub-subform (BilletCode). At this time (maybe
because of the multipe subform layers) this approach does NOT work any
longer.

2. Overall, this approach (even on a single form) seems very complicated.


So, my questions are... first, is there is an easier and more efficient way
to show only specific values in a combo box (based on the superior combo box
values)? If yes, could someone please
provide me some detailed pointers how to re-structure the data dependencies?

Second, if my approach seems to be reasonable, what may I have done wrong
with the translation from single form to multiple sub forms?


Any help is truly appreciated!!!


Thanks in advance,
Tom
Click to expand...
Click to expand...
 
Ken,

this works fabulously!!! I am totally excited about this new approach.
I have 2 more questions though -- I hope you don't mind me asking again.

However, before I ask the questions, I'll like write down the changes I have
made. These changes are (I think independent of my questions though).

1. Added tblStorage (in addition to tblSourceData). Both tables have
identical structure.
2. On frmSourceData, changed the unbound combo boxes (tblSourceData) to
bound fields of tblStorage (I did not change the row sources though).
3. Changed to AfterUpdate event to the following to accomodate immediate
refresh of the combo boxes:

Private Sub Division_Change()
Sections.Requery
Me.Sections = Me.Sections.ItemData(0)
BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub

Private Sub Sections_Change()
BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub

4. Added "=DLookUp("[Division]","tblSourceData")" as the "Default Value". I
did the same for Sections and BilletCode.


Okay, here are my questions:
1. Currently, I have all 3 combo boxes on a single subform. My goal though
is to place them on the main form (Division) which has a subform (Sections)
which has also a subform (BilletCode).

2. Based on Q1, I prefer to show the 2 subform in "datasheet" view. Once I
have done that, I verified that all dependencies work are maintained (which
it does).

However, when selecting value "A" from the combo box Division on the
mainform, I will see all subordinate children of "A" (AA, AB, AC, AD, etc)
in the datasheet view of the subform. More specifically, it list 15
records on the Section level when having selected "A". This is because the
source table list "A" 15 times which then lists 4 sections (AA, AB, AC, AD)
repeatedly because each of the 4 sections have altogether 15 billet codes
(AAA, AAB, AAC... ADA, ADB, ADC).

Listing any of the sections in this view is not really what I want though.
Instead, I don't want any records that view. I simply want to be able to
add records into the datasheet. And those values that I can add are based
on the combo box dependencies.

Wow, I hope this makes sense?! Just in case, let me put into more lament's
terms.

1. I open up the main form. At this time I see only the Division combo box
and the Section subform (which does not contain any records at this time)
2. I click on combo box Divsion and see 4 values (A, B, C, D).
3. I select value "A"
4. I move to the subform Section. I click on the + (since it is datasheet
view) and now I see only the values "AA, AB, AC, AD"). Since having moved
to the subform Sections, I also now see the Section's subform BilletCode.
5. Until having selected e.g. "AA" I did not see any BilletCodes either.
But now (with "AA" being selected), I see values "AAA, AAB, AAC" in the
combo box. I must be able to add them as new records though (rather than
just seeing them).


Oh gosh, I am not sure if this really makes sense. Spelling out these
details may cause more confusion.

Again, if you could provide me any additional pointers, I would truly
appreciate it. If you have any questions (I would after reading this),
please don't hesitate to ask. Or would it make sense for me to attached
the db. Maybe this would allow you to get a better idea of what I'm trying
to achieve.


Thanks so much again,
Tom








Ken Snell said:
The row source query for the third combo box will need to have two parts to
the criterion statement. You need to repeat the criterion of the second
combo box and add the criterion for the value in the second box.

The row source query for the second combo box should be something like this:

"Select Sections from tblSourceData Where Division=
Forms!frmSourceData!Division;"


The row source query for the third combo box should be something like this:

"Select Billets from tblSourceData Where Division=
Forms!frmSourceData!Division And Sections = Forms!frmSourceData!Sections;"


Change the After Update code for the Divisions combo box to this:

Private Sub Divisions_AfterUpdate()
Me.Sections.Requery
End Sub

Make the After Update code for the Sections combo box to be this:

Private Sub Sections_AfterUpdate()
Me.Billets.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


Based on the information listed on the solution you provided, I have
attempted
to replicate the scenario. Although, I get this to work, the results are
not
what I had expected. Let me provide the background information first and
ask
my questions at the end of this thread (below the ****s).


Background Info:

TABLE
=====

1 Table: "tblSourceData"
3 Fields: Division, Sections, BilletCode


Example Data in tblSourceData
=============================

Division Sections BilletCode
A AA AAA
A AA AAB
A AB ABA
A AB ABB
B BA BAA
B BA BAB
B BB BBA
B BB BBB


FORM
====
1 Form: "frmSourceData"
3 Combo Boxes: Division, Sections, BilletCode



Combobox "Division":
====================
Control Source = Division
Row Source Type = Field List
Row Source = tblSourceData

After Update event is the following:

Private Sub Division_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Division
strSQL = strSQL & " from tblSourceData"
Me!Sections.RowSourceType = "Table/Query"
Me!Sections.RowSource = strSQL
End Sub


Combobox "Sections":
====================
Control Source = Sections
Row Source Type = not applicable
Row Source = not applicable

After Update event is the following:

Private Sub Sections_AfterUpdate()
Me!Sections.Requery
End Sub


Others:
=======

I did not know what to do w/ the following:

"Select Division, Sections from tblSourceData Where Division=
Forms!frmSourceData!Division;"



**** Questions *****

Currently, I see "Division, Sections, BilletCodes" in the combobox
"Division".

However, based on the example data, I wanted to see the following:
- when selecting value "A" in combobox "Division",
- then populate values "AA, AB" into combobox "Section"
- then when selecting value "AA" in combobox "Section"
- then populated value "AAA, AAB" into combobox "BilletCode"
- same for the others... e.g. select B, then see only BA & BB,
select BA, then see only BAA & BAB


So my questions are:
1. Is there a chance to modify the scenario to accomodate this process?
2. What do I need to do w/ the "Select Division, Sections from tblSourceData
Where
Division= Forms!frmSourceData!Division;"
3. If this is possible, how do I modify the 2nd level (Sections) to
accomodate
the 3rd level (BilletCode)?



Thanks so much in advance,
Tom





Tom said:
Ken:

I'll give it a try.... looks so much easier then my way of doing it.
Hopefully it works w/ subforms, too.
Click to expand...
(((tbl_NatoDivisionsSections.Division)=[Forms]![frmUpdateInfo]![Division]))(((tbl_NatoDivisionsSectionsBillets.Sections)=[Forms]![frmUpdateInfo]![Secti
ons]))
ORDER BY tbl_NatoDivisionsSectionsBillets.BilletCode;


4. qryUpdateInfo
SELECT tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections,
tbl_UpdateInfo.BilletCode
FROM tbl_UpdateInfo
ORDER BY tbl_UpdateInfo.Division, tbl_UpdateInfo.Sections DESC;



FORMS:

Again, in the form frmUpdatInfo, I have placed the 3 combo boxes.

For both the Division and Section combo box, I placed the following
Click to expand...
code
in
the AfterChange event handler:


&&&&&&&&&&&&&&

Private Sub Division_Change()
Dim Division As ComboBox, Sections As ComboBox, BilletCode As ComboBox
Set Division = Forms![frmUpdateInfo].[Division]
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

Sections.Requery
Me.Sections = Me.Sections.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

Private Sub Sections_Change()
Dim Sections As ComboBox, BilletCode As ComboBox
Set Sections = Forms![frmUpdateInfo].[Sections]
Set BilletCode = Forms![frmUpdateInfo].[BilletCode]
Echo False

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)

Echo True
End Sub

&&&&&&&&&&&&&&




Okay, here now is the problem:

1. In a different application, I have am using a main form (Division)
Click to expand...
plus
a
subform (Section) plus a sub-subform (BilletCode). At this time (maybe
because of the multipe subform layers) this approach does NOT work any
longer.

2. Overall, this approach (even on a single form) seems very complicated.


So, my questions are... first, is there is an easier and more
Click to expand...
efficient
way
to show only specific values in a combo box (based on the superior
Click to expand...
combo
box
values)? If yes, could someone please
provide me some detailed pointers how to re-structure the data dependencies?

Second, if my approach seems to be reasonable, what may I have done wrong
with the translation from single form to multiple sub forms?


Any help is truly appreciated!!!


Thanks in advance,
Tom
Click to expand...
Click to expand...
Click to expand...
 
I typically would not use the Change event of the Sections combo box to do
the requery of the Billets combo box -- I'd prefer to use the AfterUpdate
event. But if it's working for your needs, then ok.

Do I understand correctly that you are seeing more records in the Sections
subform than you want to see...because each record is being duplicated
(multiplicated!) based on the number of Billet codes?

I don't know what you're using as the Record source of the Sections subform,
so I will assume that you're using the tblBilletCodes table (or a query
based on it) as the record source? Without knowing this detail, take the
rest of my reply as a starting point for continued discussion and
refinement.

You indicated previously that you have Divisions, Sections, and Billets all
in one table (tblBilletCodes). And your sections appear to be "named" as the
division code plus a "sequential" letter. Your billets appear to be named as
the division code plus the section code plus a "sequential letter". (I am
assuming that you're presenting real data here, not artificial values for
the purpose of asking the question.) As such, you are storing redundant data
within at least two fields.

I'm not sure how Sections and Billets completely relate to Divisions and
Sections respectively?

Your "multiplicative" display likely is resulting because you should be
using tblSections as the table for the source data (or better, using a query
based on tblSections) in the Sections subform and not tblBilletCodes.

If the values you've presented for Division, Section, and Billet are exactly
as you state (letters that are used the same way in each of the three
fields), then what you may want to do is change your table structure to
something like this (similar to what you've got now, but with a bit of a
"twist"):

tblLetterCodes (is just a list of the 26 alphabetical characters)
LetterID (primary key) - Text format

tblDivisions
DivisionID (primary key) -- foreign key to tblLetterCodes
DivisionName

tblSections
DivisionID (composite primary key with SectionID) -- foreign key to
tblLetterCodes
SectionID (composite primary key with DivisionID) -- foreign key to
tblLetterCodes
SectionName

tblBillets (contains all the combinations of division, section, and billet
that have been assigned)
DivisionID (composite primary key with SectionID and BilletID) --
foreign key to tblLetterCodes
SectionID (composite primary key with DivisionIDand BilletID) --
foreign key to tblLetterCodes
BilletID (composite primary key with DivisionID and SectionID) --
foreign key to tblLetterCodes
BilletName

You can use a query to "display" the section "code":
SELECT [DivisionID], [SectionID], [DivisionID] & [SectionID] AS Section
FROM tblSections;

You can use a query to "display" the billet "code":
SELECT [DivisionID], [SectionID], [BilletID],
[DivisionID] & [SectionID] & [BilletID] AS Billet
FROM tblBillets;

Note that the above queries are updatable and can be used for editing data
in the table. You can add WHERE clauses to them so that the data are
filtered to show just the sections for the chosen division, and just the
billets for the chosen division and section. To edit the data on your form,
it's a matter of binding the combo box (which uses either a query that
returns the division or section code in the Row Source) to the appropriate
field in the record source of the form or subform.

To add data via the subform, your combo box needs to let you choose a letter
that is to be assigned as the section identifier for the section code. Thus,
the exact query that you'd want for the combo box needs to let you have
choices beyond those that result when you filter the the combo box's list
based on a division choice. Thus, in some respects, this works at "cross
purposes" to what you wanted.

So, I recommend that you think about two forms. One is used to select from
already assigned divisions, sections, and billets for the purpose of using
those assignations in some way (filling out a purchase order, filling out a
requisition, etc.). This is the type of setup that you started with, and for
which you've set up the dependent combo boxes.

And then have a second form that allows you to assign new divisions,
sections, and billets. In this case, your combo boxes should not be
dependent on each other. Instead, the row source for the combo boxes (all
three) would be something like this:
SELECT LetterID FROM tblLetterCodes;

You could increase the "fanciness" by filtering out already used letters by
changing the queries for the combo boxes in this way (substituting correct
form and control names as needed, of course):

For the divisions combo box:
SELECT LetterID FROM tblLetterCodes
WHERE LetterID NOT IN
(SELECT DivisionID FROM tblDivisions);

For the sections combo box:
SELECT LetterID FROM tblLetterCodes
WHERE LetterID NOT IN
(SELECT SectionID FROM tblSections
WHERE DivisionID =
Forms!FormName!cboDivisions);

For the billets combo box:
SELECT LetterID FROM tblLetterCodes
WHERE LetterID NOT IN
(SELECT SectionID FROM tblSections
WHERE DivisionID =
Forms!FormName!cboDivisions AND
WHERE SectionID =
Forms!FormName!cboSections);

Just some ideas to perhaps suggest alternative approaches.
--

Ken Snell
<MS ACCESS MVP>


Tom said:
Ken,

this works fabulously!!! I am totally excited about this new approach.
I have 2 more questions though -- I hope you don't mind me asking again.

However, before I ask the questions, I'll like write down the changes I have
made. These changes are (I think independent of my questions though).

1. Added tblStorage (in addition to tblSourceData). Both tables have
identical structure.
2. On frmSourceData, changed the unbound combo boxes (tblSourceData) to
bound fields of tblStorage (I did not change the row sources though).
3. Changed to AfterUpdate event to the following to accomodate immediate
refresh of the combo boxes:

Private Sub Division_Change()
Sections.Requery
Me.Sections = Me.Sections.ItemData(0)
BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub

Private Sub Sections_Change()
BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub

4. Added "=DLookUp("[Division]","tblSourceData")" as the "Default Value". I
did the same for Sections and BilletCode.


Okay, here are my questions:
1. Currently, I have all 3 combo boxes on a single subform. My goal though
is to place them on the main form (Division) which has a subform (Sections)
which has also a subform (BilletCode).

2. Based on Q1, I prefer to show the 2 subform in "datasheet" view. Once I
have done that, I verified that all dependencies work are maintained (which
it does).

However, when selecting value "A" from the combo box Division on the
mainform, I will see all subordinate children of "A" (AA, AB, AC, AD, etc)
in the datasheet view of the subform. More specifically, it list 15
records on the Section level when having selected "A". This is because the
source table list "A" 15 times which then lists 4 sections (AA, AB, AC, AD)
repeatedly because each of the 4 sections have altogether 15 billet codes
(AAA, AAB, AAC... ADA, ADB, ADC).

Listing any of the sections in this view is not really what I want though.
Instead, I don't want any records that view. I simply want to be able to
add records into the datasheet. And those values that I can add are based
on the combo box dependencies.

Wow, I hope this makes sense?! Just in case, let me put into more lament's
terms.

1. I open up the main form. At this time I see only the Division combo box
and the Section subform (which does not contain any records at this time)
2. I click on combo box Divsion and see 4 values (A, B, C, D).
3. I select value "A"
4. I move to the subform Section. I click on the + (since it is datasheet
view) and now I see only the values "AA, AB, AC, AD"). Since having moved
to the subform Sections, I also now see the Section's subform BilletCode.
5. Until having selected e.g. "AA" I did not see any BilletCodes either.
But now (with "AA" being selected), I see values "AAA, AAB, AAC" in the
combo box. I must be able to add them as new records though (rather than
just seeing them).


Oh gosh, I am not sure if this really makes sense. Spelling out these
details may cause more confusion.

Again, if you could provide me any additional pointers, I would truly
appreciate it. If you have any questions (I would after reading this),
please don't hesitate to ask. Or would it make sense for me to attached
the db. Maybe this would allow you to get a better idea of what I'm trying
to achieve.


Thanks so much again,
Tom
Click to expand...
 
Ken:

You're awesome... thanks so much for the detailed information. I now have
to digest the information and will work on your recommendations either today
or tomorrow.

I am confident that I will into some additional "snags"... so I'd appreciate
if you could keep watching this thread for additional postings.

Again, thousands thanks. I'm getting there...slowly but surely.

--
Tom


Ken Snell said:
I typically would not use the Change event of the Sections combo box to do
the requery of the Billets combo box -- I'd prefer to use the AfterUpdate
event. But if it's working for your needs, then ok.

Do I understand correctly that you are seeing more records in the Sections
subform than you want to see...because each record is being duplicated
(multiplicated!) based on the number of Billet codes?

I don't know what you're using as the Record source of the Sections subform,
so I will assume that you're using the tblBilletCodes table (or a query
based on it) as the record source? Without knowing this detail, take the
rest of my reply as a starting point for continued discussion and
refinement.

You indicated previously that you have Divisions, Sections, and Billets all
in one table (tblBilletCodes). And your sections appear to be "named" as the
division code plus a "sequential" letter. Your billets appear to be named as
the division code plus the section code plus a "sequential letter". (I am
assuming that you're presenting real data here, not artificial values for
the purpose of asking the question.) As such, you are storing redundant data
within at least two fields.

I'm not sure how Sections and Billets completely relate to Divisions and
Sections respectively?

Your "multiplicative" display likely is resulting because you should be
using tblSections as the table for the source data (or better, using a query
based on tblSections) in the Sections subform and not tblBilletCodes.

If the values you've presented for Division, Section, and Billet are exactly
as you state (letters that are used the same way in each of the three
fields), then what you may want to do is change your table structure to
something like this (similar to what you've got now, but with a bit of a
"twist"):

tblLetterCodes (is just a list of the 26 alphabetical characters)
LetterID (primary key) - Text format

tblDivisions
DivisionID (primary key) -- foreign key to tblLetterCodes
DivisionName

tblSections
DivisionID (composite primary key with SectionID) -- foreign key to
tblLetterCodes
SectionID (composite primary key with DivisionID) -- foreign key to
tblLetterCodes
SectionName

tblBillets (contains all the combinations of division, section, and billet
that have been assigned)
DivisionID (composite primary key with SectionID and BilletID) --
foreign key to tblLetterCodes
SectionID (composite primary key with DivisionIDand BilletID) --
foreign key to tblLetterCodes
BilletID (composite primary key with DivisionID and SectionID) --
foreign key to tblLetterCodes
BilletName

You can use a query to "display" the section "code":
SELECT [DivisionID], [SectionID], [DivisionID] & [SectionID] AS Section
FROM tblSections;

You can use a query to "display" the billet "code":
SELECT [DivisionID], [SectionID], [BilletID],
[DivisionID] & [SectionID] & [BilletID] AS Billet
FROM tblBillets;

Note that the above queries are updatable and can be used for editing data
in the table. You can add WHERE clauses to them so that the data are
filtered to show just the sections for the chosen division, and just the
billets for the chosen division and section. To edit the data on your form,
it's a matter of binding the combo box (which uses either a query that
returns the division or section code in the Row Source) to the appropriate
field in the record source of the form or subform.

To add data via the subform, your combo box needs to let you choose a letter
that is to be assigned as the section identifier for the section code. Thus,
the exact query that you'd want for the combo box needs to let you have
choices beyond those that result when you filter the the combo box's list
based on a division choice. Thus, in some respects, this works at "cross
purposes" to what you wanted.

So, I recommend that you think about two forms. One is used to select from
already assigned divisions, sections, and billets for the purpose of using
those assignations in some way (filling out a purchase order, filling out a
requisition, etc.). This is the type of setup that you started with, and for
which you've set up the dependent combo boxes.

And then have a second form that allows you to assign new divisions,
sections, and billets. In this case, your combo boxes should not be
dependent on each other. Instead, the row source for the combo boxes (all
three) would be something like this:
SELECT LetterID FROM tblLetterCodes;

You could increase the "fanciness" by filtering out already used letters by
changing the queries for the combo boxes in this way (substituting correct
form and control names as needed, of course):

For the divisions combo box:
SELECT LetterID FROM tblLetterCodes
WHERE LetterID NOT IN
(SELECT DivisionID FROM tblDivisions);

For the sections combo box:
SELECT LetterID FROM tblLetterCodes
WHERE LetterID NOT IN
(SELECT SectionID FROM tblSections
WHERE DivisionID =
Forms!FormName!cboDivisions);

For the billets combo box:
SELECT LetterID FROM tblLetterCodes
WHERE LetterID NOT IN
(SELECT SectionID FROM tblSections
WHERE DivisionID =
Forms!FormName!cboDivisions AND
WHERE SectionID =
Forms!FormName!cboSections);

Just some ideas to perhaps suggest alternative approaches.
--

Ken Snell
<MS ACCESS MVP>


Tom said:
Ken,

this works fabulously!!! I am totally excited about this new approach.
I have 2 more questions though -- I hope you don't mind me asking again.

However, before I ask the questions, I'll like write down the changes I have
made. These changes are (I think independent of my questions though).

1. Added tblStorage (in addition to tblSourceData). Both tables have
identical structure.
2. On frmSourceData, changed the unbound combo boxes (tblSourceData) to
bound fields of tblStorage (I did not change the row sources though).
3. Changed to AfterUpdate event to the following to accomodate immediate
refresh of the combo boxes:

Private Sub Division_Change()
Sections.Requery
Me.Sections = Me.Sections.ItemData(0)
BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub

Private Sub Sections_Change()
BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub

4. Added "=DLookUp("[Division]","tblSourceData")" as the "Default
Click to expand...
Value".
I
did the same for Sections and BilletCode.


Okay, here are my questions:
1. Currently, I have all 3 combo boxes on a single subform. My goal though
is to place them on the main form (Division) which has a subform (Sections)
which has also a subform (BilletCode).

2. Based on Q1, I prefer to show the 2 subform in "datasheet" view.
Click to expand...
Once
I
have done that, I verified that all dependencies work are maintained (which
it does).

However, when selecting value "A" from the combo box Division on the
mainform, I will see all subordinate children of "A" (AA, AB, AC, AD, etc)
in the datasheet view of the subform. More specifically, it list 15
records on the Section level when having selected "A". This is because the
source table list "A" 15 times which then lists 4 sections (AA, AB, AC, AD)
repeatedly because each of the 4 sections have altogether 15 billet codes
(AAA, AAB, AAC... ADA, ADB, ADC).

Listing any of the sections in this view is not really what I want though.
Instead, I don't want any records that view. I simply want to be able to
add records into the datasheet. And those values that I can add are based
on the combo box dependencies.

Wow, I hope this makes sense?! Just in case, let me put into more lament's
terms.

1. I open up the main form. At this time I see only the Division combo box
and the Section subform (which does not contain any records at this time)
2. I click on combo box Divsion and see 4 values (A, B, C, D).
3. I select value "A"
4. I move to the subform Section. I click on the + (since it is datasheet
view) and now I see only the values "AA, AB, AC, AD"). Since having moved
to the subform Sections, I also now see the Section's subform BilletCode.
5. Until having selected e.g. "AA" I did not see any BilletCodes either.
But now (with "AA" being selected), I see values "AAA, AAB, AAC" in the
combo box. I must be able to add them as new records though (rather than
just seeing them).


Oh gosh, I am not sure if this really makes sense. Spelling out these
details may cause more confusion.

Again, if you could provide me any additional pointers, I would truly
appreciate it. If you have any questions (I would after reading this),
please don't hesitate to ask. Or would it make sense for me to attached
the db. Maybe this would allow you to get a better idea of what I'm trying
to achieve.


Thanks so much again,
Tom
Click to expand...
Click to expand...
 
Back
Top