Cascading Combo Boxes -- Part 2

R

rich

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
 
S

solex

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 said:
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
Click to expand...
 
T

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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cascading Combo Boxes 1
Data Dependencies between Combo Boxes 7
Limit content of combo boxes 1
Need help cascading 5 combo boxes 12
Question about Cascading Combo Boxes 6
Cascading Combo Boxes - Expression is too complex to be evaluated ... 4
cascading combo box 20
Limit content of combo boxes 1

Top