Limit content of combo boxes

[Tom]

In an earlier thread, Ken Snell pointed out that the solution on
http://www.mvps.org/access/forms/frm0028.htm might provide me the answer to
my problem.

I replicated the work (w/ the modifications below) but the results are not
what I 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 are:
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

 

[Roger Carlson]

On my website (see sig below) is a small sample database called
"CascadingCombos.mdb" which might be of some help.