If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cascading Combo Boxes
I need some help w/ cascading combo boxes in conjunction with subforms.
I posted a similar thread before, but I still haven't been able to get this to work. (Ken Snell was so kind to get me up to speed until this point and I'm not sure if I truly understood his additional recommendations in respect to using subforms). Before I post my question, I'd like to provide some detailed information about the tables, forms, subforms, etc. The description is quite lengthy, so please bare w/ me. I have a 3-tier One-To-Many table structure. - Division is "parent" of SectionCode - SectionCode is "parent" of BilletCode TABLE (and relationships): ************************** tblDivisions ============ DivisionID (Autonumber) Division (Text) tblSections =========== SectionID (Autonumber) DivisionIDfk (Number) SectionCode (Text) tblBilletCodes ============== BilletCodeID (Autonumber) SectionIDfk (Number) BilletCode (Text) FORMS AND SUBFORMS: ******************* First, 1. I have a mainform (frmBoards). This form contains general board information. Then, 2. tblDivisions is stored on frmDivisions 3. tblSections is stored on frmSections 4. tblBilletCodes is stored on frmBillets then A. frmDivisions has a subform (frmSections) B. frmSections has a subform (frmBillets) NOTE: all 3 forms (frmDivisions, frmSections, frmBillets) are displayed in "Datasheet" view DATA DEPENDENCIES FOR THE COMBO BOXES ************************************* At this time (for testing purposes), I have the 3 combo boxes for Division, SectionCode, and BilletCode on a single form. The cascading dependencies for Divisions, Sections, and Billetcodes are better understood with the following geographical example: Let's say: - Division = State - SectionCode = City - BilletCode = Community For instance, the values in the combo must are dynamic based on their parent value(s): - If State "CA" - then City "San Francisco" or "Los Angeles" - then Community "SF North", "SF South", "LA North", "LA South" - If State "FL" - then City "Miami" or "Jacksonville" - then Community "MIA North", "MIA South", "JVL North", "JVL South" etc.... the same principle applies for my 3-tier relationship betwen Division, SectionCodes, and BilletCodes 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 |
#2
|
|||
|
|||
Cascading Combo Boxes
Tom,
One thing is not clear. Do you want to KEEP the comboboxes and MOVE them to the subforms, or do you want to REPLACE the comboboxes with the subforms -----Original Message----- I need some help w/ cascading combo boxes in conjunction with subforms. I posted a similar thread before, but I still haven't been able to get this to work. (Ken Snell was so kind to get me up to speed until this point and I'm not sure if I truly understood his additional recommendations in respect to using subforms). Before I post my question, I'd like to provide some detailed information about the tables, forms, subforms, etc. The description is quite lengthy, so please bare w/ me. I have a 3-tier One-To-Many table structure. - Division is "parent" of SectionCode - SectionCode is "parent" of BilletCode TABLE (and relationships): ************************** tblDivisions ============ DivisionID (Autonumber) Division (Text) tblSections =========== SectionID (Autonumber) DivisionIDfk (Number) SectionCode (Text) tblBilletCodes ============== BilletCodeID (Autonumber) SectionIDfk (Number) BilletCode (Text) FORMS AND SUBFORMS: ******************* First, 1. I have a mainform (frmBoards). This form contains general board information. Then, 2. tblDivisions is stored on frmDivisions 3. tblSections is stored on frmSections 4. tblBilletCodes is stored on frmBillets then A. frmDivisions has a subform (frmSections) B. frmSections has a subform (frmBillets) NOTE: all 3 forms (frmDivisions, frmSections, frmBillets) are displayed in "Datasheet" view DATA DEPENDENCIES FOR THE COMBO BOXES ************************************* At this time (for testing purposes), I have the 3 combo boxes for Division, SectionCode, and BilletCode on a single form. The cascading dependencies for Divisions, Sections, and Billetcodes are better understood with the following geographical example: Let's say: - Division = State - SectionCode = City - BilletCode = Community For instance, the values in the combo must are dynamic based on their parent value(s): - If State "CA" - then City "San Francisco" or "Los Angeles" - then Community "SF North", "SF South", "LA North", "LA South" - If State "FL" - then City "Miami" or "Jacksonville" - then Community "MIA North", "MIA South", "JVL North", "JVL South" etc.... the same principle applies for my 3-tier relationship betwen Division, SectionCodes, and BilletCodes 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 . |
Thread Tools | |
Display Modes | |
|
|