Limit content of combo boxes

[Tom]

In an earlier thread, Ken Snell suggested that the information listed at
http://www.mvps.org/access/forms/frm0028.htm might solve my problem with
data dependencies between combo boxes.

I successfully replicated the scenario; however, the results are not what I
expected.

Below is my modified version following the instructions URL above.

The suggested scenario allows to select values from a combo box. Let's say
these values are Country, State, City.

If I click on ComboBox1 and select, ComboBox2 shows all countries. If I
were to select States from ComboBox 1, ComboBox2 then shows only states ...
and so on.

However, my problem is a bit different. Let's say I select State "VA" from
ComboBox1, I only want to see cities that are of the state VA. Switching
to e.g. "TX" would give me then only cities in Texas.


So my questions are as follows:
1. Is there a chance to modify the scenario below 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)?


*****************************


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;"



**** Additional INfo *****

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




Thanks so much in advance,
Tom

 

[Thomas]

Hello Tom,

What I have hear this type of ComboBox is a Cascading ComboBox.
Use Queries to limit the data to each ComboBox.

Here it goes

You already have the table
Table: "tblSourceData"
Fields: Division, Sections, BilletCode

you need to create 3 Queries

Query 1
Field: Division
Table: tblSourceData
Total: GroupBy
Sort: Ascending
Show: Is checked


Query 2
Field: Sections Division
Table: tblSourceData tblSourceData
Sort:
Show: Is checked is NOT checked
Criteria [Forms]![Form1]![combo0]



Query 3
Field: BilletCode Sections
Table: tblSourceData tblSourceData
Sort:
Show: Is checked is NOT checked
Criteria [Forms]![Form1]![combo2]


Now create a unbound Form the name of the Form Is (Form1)

Add 3 ComboBoxes (Unbound)
"if the ComboBox wizard pops-up"
(click) I will type the value I want

Name the ComboBoxes
1st one (Division)
2nd one (Sections)
3rd one (BilletCode)

Next right click on the 1st Unbound ComboBox (Division)
select properties
next click on the All tab
The comboBox name should be Combo0
Select Row Source type as Table/Query
Select Row Source as Query1


Next right click on the 2nd Unbound ComboBox (Sections)
select properties
next click on the All tab
The comboBox name should be Combo2
Select Row Source type as Table/Query
Select Row Source as Query2



Next right click on the 3rd Unbound ComboBox (BilletCode)
select properties
next click on the All tab
The comboBox name should be Combo4
Select Row Source type as Table/Query
Select Row Source as Query3


Now for the Code to Requery the ComboBoxes
place this code in the ComboBoxes AfterUpdate Event
____________________________________
Private Sub Combo0_AfterUpdate()
Me!Combo2.Requery

End Sub
____________________________________
Private Sub Combo2_AfterUpdate()
Me!Combo4.Requery
End Sub

Hope this helps

Thomas