Cascading Combo Boxes -- Part 2

THREAD IS CONTINUED FROM POST "CASCADING COMBO BOXES - PART 1"
================================================== ============





CURRENT COMBO BOXES (SINGLE FORM)
*********************************

Here's how the cascading combos work on a SINGLE form (which I need to
translate into
the mentioned forms and subforms).

NOTE: for testing purposes, I do pull the source data from their respective
tables
I currently store them into a temporary data storage table (tblComboTest).



Single Form is called = "ComboTest"
===================================
- RecordSource = tblComboTest
- contains combos "Division", "SectionCode", "BilletCode"


Combo "Division"
================
- Control Source = Division
- Row Source Type = Table/Query
- Row Source = SELECT tbl_Divisions.Division
FROM tbl_Divisions GROUP BY tbl_Divisions.Division
ORDER BY tbl_Divisions.Division;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes

Event: On Change (below &&&s):

&&&&&&&&&&&
Private Sub Division_Change()
SectionCode.Requery
Me.SectionCode = Me.SectionCode.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub
&&&&&&&&&&&


Combo "Section"
===============
- Control Source = Section
- Row Source Type = Table/Query
- Row Source = SELECT DISTINCT tbl_Sections.SectionCode
FROM tbl_Divisions INNER JOIN tbl_Sections
ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk
WHERE
(((tbl_Divisions.Division)=[Forms]![ComboTest]![Division]))
ORDER BY tbl_Sections.SectionCode;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes

Event: On Change (below &&&s):

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



Combo "BilletCodes"
===================
- Control Source = BilletCode
- Row Source Type = Table/Query
- Row Source = SELECT tbl_BilletCodes.BilletCode, tbl_Divisions.Division,
tbl_Sections.SectionCode FROM (tbl_Divisions INNER JOIN tbl_Sections
ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN tbl_BilletCodes ON
tbl_Sections.SectionID = tbl_BilletCodes.SectionIDfk
WHERE (((tbl_Divisions.Division)=[Forms]![ComboTest]![Division])
AND ((tbl_Sections.SectionCode)=[Forms]![ComboTest]![SectionCode]))
ORDER BY tbl_BilletCodes.BilletCode;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes




Again, I apologize for this very lengthy post; however, I believe it may
eliminate
some questions in the thread (besides it might provide good reference for
anyone
else who needs to recreate this scenario on a single form).

Alright, at this time, my question is straightforward... how do I modify
this
row sources & On Change event handlers to get the same results using
multiple subforms?


Thanks so much in advance,
Tom

rich,

Am I to guess that you want all three subforms on one form and if you change
the record pointer on any of the "parent" forms that the system should
automatically update the child forms?

It can be done but not without some coding. My question is what is wrong
with the combo boxes that you have already created and tested?

Dan



"rich" wrote in message
...
THREAD IS CONTINUED FROM POST "CASCADING COMBO BOXES - PART 1"
================================================== ============





CURRENT COMBO BOXES (SINGLE FORM)
*********************************

Here's how the cascading combos work on a SINGLE form (which I need to
translate into
the mentioned forms and subforms).

NOTE: for testing purposes, I do pull the source data from their
respective
tables
I currently store them into a temporary data storage table (tblComboTest).



Single Form is called = "ComboTest"
===================================
- RecordSource = tblComboTest
- contains combos "Division", "SectionCode", "BilletCode"


Combo "Division"
================
- Control Source = Division
- Row Source Type = Table/Query
- Row Source = SELECT tbl_Divisions.Division
FROM tbl_Divisions GROUP BY tbl_Divisions.Division
ORDER BY tbl_Divisions.Division;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes

Event: On Change (below &&&s):

&&&&&&&&&&&
Private Sub Division_Change()
SectionCode.Requery
Me.SectionCode = Me.SectionCode.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub
&&&&&&&&&&&


Combo "Section"
===============
- Control Source = Section
- Row Source Type = Table/Query
- Row Source = SELECT DISTINCT tbl_Sections.SectionCode
FROM tbl_Divisions INNER JOIN tbl_Sections
ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk
WHERE
(((tbl_Divisions.Division)=[Forms]![ComboTest]![Division]))
ORDER BY tbl_Sections.SectionCode;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes

Event: On Change (below &&&s):

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



Combo "BilletCodes"
===================
- Control Source = BilletCode
- Row Source Type = Table/Query
- Row Source = SELECT tbl_BilletCodes.BilletCode, tbl_Divisions.Division,
tbl_Sections.SectionCode FROM (tbl_Divisions INNER JOIN
tbl_Sections
ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN tbl_BilletCodes ON
tbl_Sections.SectionID = tbl_BilletCodes.SectionIDfk
WHERE (((tbl_Divisions.Division)=[Forms]![ComboTest]![Division])
AND
((tbl_Sections.SectionCode)=[Forms]![ComboTest]![SectionCode]))
ORDER BY tbl_BilletCodes.BilletCode;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes




Again, I apologize for this very lengthy post; however, I believe it may
eliminate
some questions in the thread (besides it might provide good reference for
anyone
else who needs to recreate this scenario on a single form).

Alright, at this time, my question is straightforward... how do I modify
this
row sources & On Change event handlers to get the same results using
multiple subforms?


Thanks so much in advance,
Tom

Dan, thanks for the prompt reply.

Here's the relationship of the forms/subforms.

1. Mainform.... which has
2. Subform "frmDivision" which has
3. Subform "frmSections" which has
4. Subform "frmBillets"


Again, the successfully tested 3 combo boxes work currently on a single
dummy form (for testing purposes). However, the above structure (1 to 4)
is what I need to achieve.

Hmh, I did include some coding in the post(s). Please make sure to review
both posts
("Cascading Combo Boxes -- Part 1" & "Cascading Combo Boxes -- Part 2").
Altogether, this was a very lengthy thread so I had to cut into two parts.


Tom

(I used the PC of my team mate Rich earlier)






"solex" wrote in message
...
rich,

Am I to guess that you want all three subforms on one form and if you
change
the record pointer on any of the "parent" forms that the system should
automatically update the child forms?

It can be done but not without some coding. My question is what is wrong
with the combo boxes that you have already created and tested?

Dan



"rich" wrote in message
...
THREAD IS CONTINUED FROM POST "CASCADING COMBO BOXES - PART 1"
================================================== ============





CURRENT COMBO BOXES (SINGLE FORM)
*********************************

Here's how the cascading combos work on a SINGLE form (which I need to
translate into
the mentioned forms and subforms).

NOTE: for testing purposes, I do pull the source data from their
respective
tables
I currently store them into a temporary data storage table
(tblComboTest).



Single Form is called = "ComboTest"
===================================
- RecordSource = tblComboTest
- contains combos "Division", "SectionCode", "BilletCode"


Combo "Division"
================
- Control Source = Division
- Row Source Type = Table/Query
- Row Source = SELECT tbl_Divisions.Division
FROM tbl_Divisions GROUP BY tbl_Divisions.Division
ORDER BY tbl_Divisions.Division;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes

Event: On Change (below &&&s):

&&&&&&&&&&&
Private Sub Division_Change()
SectionCode.Requery
Me.SectionCode = Me.SectionCode.ItemData(0)

BilletCode.Requery
Me.BilletCode = Me.BilletCode.ItemData(0)
End Sub
&&&&&&&&&&&


Combo "Section"
===============
- Control Source = Section
- Row Source Type = Table/Query
- Row Source = SELECT DISTINCT tbl_Sections.SectionCode
FROM tbl_Divisions INNER JOIN tbl_Sections
ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk
WHERE
(((tbl_Divisions.Division)=[Forms]![ComboTest]![Division]))
ORDER BY tbl_Sections.SectionCode;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes

Event: On Change (below &&&s):

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



Combo "BilletCodes"
===================
- Control Source = BilletCode
- Row Source Type = Table/Query
- Row Source = SELECT tbl_BilletCodes.BilletCode,
tbl_Divisions.Division,
tbl_Sections.SectionCode FROM (tbl_Divisions INNER JOIN
tbl_Sections
ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk)
INNER JOIN tbl_BilletCodes ON
tbl_Sections.SectionID = tbl_BilletCodes.SectionIDfk
WHERE (((tbl_Divisions.Division)=[Forms]![ComboTest]![Division])
AND
((tbl_Sections.SectionCode)=[Forms]![ComboTest]![SectionCode]))
ORDER BY tbl_BilletCodes.BilletCode;
- Bound Column = 1
- Limit to List = No
- Auto Expand = Yes




Again, I apologize for this very lengthy post; however, I believe it may
eliminate
some questions in the thread (besides it might provide good reference
for
anyone
else who needs to recreate this scenario on a single form).

Alright, at this time, my question is straightforward... how do I modify
this
row sources & On Change event handlers to get the same results using
multiple subforms?


Thanks so much in advance,
Tom