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
|
|||
|
|||
Data Dependencies between Combo Boxes
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 |
#2
|
|||
|
|||
Data Dependencies between Combo Boxes
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 "Tom" wrote in message ... 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 |
#3
|
|||
|
|||
Data Dependencies between Combo Boxes
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" wrote in message ... 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 "Tom" wrote in message ... 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 |
#4
|
|||
|
|||
Data Dependencies between Combo Boxes
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 a 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" wrote in message ... 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" wrote in message ... 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 "Tom" wrote in message ... 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 |
#5
|
|||
|
|||
Data Dependencies between Combo Boxes
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 "Tom" wrote in message ... 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 a 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" wrote in message ... 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" wrote in message ... 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 "Tom" wrote in message ... 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 |
#6
|
|||
|
|||
Data Dependencies between Combo Boxes
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" wrote in message ... 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 "Tom" wrote in message ... 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 a 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" wrote in message ... 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" wrote in message ... 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 "Tom" wrote in message ... 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 |
#7
|
|||
|
|||
Data Dependencies between Combo Boxes
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" wrote in message ... 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 |
#8
|
|||
|
|||
Data Dependencies between Combo Boxes
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|