Dynamic Cascading Combo Box

[Userdd]

There is one table comprises of 16 columns. In a form, i need to keep two combo boxes.
combo box 1 : displays the field names in the table. User will select any one filed from that.
-achieved
combo box 2: should display the distinct values for the field selected in combo box 1.
??????? how to do tht ??????
eg: table : series no | revision no | catalog
A | 1 | VBN
B | 2 | BNJ
A | 1 | GHU


a) Field name selected : Series No
combo box 2: shud display the list in combo box as ----- A
B
b) Field name selected : revision No
cb2 shud display the list in combo box as -----1
2

[June7]

As far as I know must use VBA code to set the RowSource. Here is example from my project.

With Combo2
Select Case Combo1
Case ""
.Value = Null
.RowSourceType = ""
.RowSource = ""
Case "State Number"
.RowSourceType = "Table/Query"
.RowSource = "SELECT DISTINCT StateNum FROM Submit LEFT JOIN Projects ON Submit.ProjRecID=Projects.ProjRecID WHERE Not Projects.StateNum Is Null ORDER BY Projects.StateNum;"
Case "Sample Type"
.RowSourceType = "Value List"
.RowSource = "Acceptance;Independent Assurance;Information;Preconstruction;Quality"
Case "Material Type"
.RowSourceType = "Table/Query"
.RowSource = "SELECT MaterialType FROM MaterialType WHERE Active=True;"
Me.cbxTest.RowSource = "SELECT TestNum FROM MaterialTest WHERE MaterialType=[cbxFor" & Right(strSelection, 1) & "];"
Case "Program Code"
.RowSourceType = "Table/Query"
.RowSource = "SELECT ProgramCode FROM ProgramCodes;"
Case "Item Number"
.RowSourceType = "Table/Query"
.RowSource = "SELECT DISTINCT ItemNum FROM Submit;"
End Select
End With
Combo2.Requery

[Userdd]

Thank you !
It Worked...

Following is the code i used, query was quite simple since my data is in single table.

Fieldtoselect - Combo box 1
distinValue - Combo box 2

Private Sub Fieldtoselect_Change()
distinValue.Value = Null
With distinValue
Select Case Fieldtoselect
Case ""
.Value = Null
.RowSourceType = ""
.RowSource = ""
Case "Catalog"
.RowSourceType = "Table/Query"
.RowSource = "SELECT DISTINCT Catalog FROM Drawing;"
Case "Series No"
.RowSourceType = "Table/Query"
.RowSource = "SELECT DISTINCT [Series No] FROM Drawing;"
Case "Revision No"
.RowSourceType = "Table/Query"
.RowSource = "SELECT DISTINCT [Revision No] FROM Drawing;"
End Select
End With
distinValue.Requery
End Sub

[June7]

My sample code is for a search form, not data entry, so some of it probably doesn't apply to your case, such as the empty string case. Since your source field names are the RowSource and RowSourceType is the same for all, can simplify even further:
Private Sub Fieldtoselect_Change()
distinValue.RowSource = "SELECT DISTINCT [" & Fieldtoselect & "] FROM Drawing ORDER BY [" & Fieldtoselect & "];"
distinValue.Requery
End Sub

The Change event works okay? I would have used AfterUpdate.

[Userdd]

Actually am also using for the search form, so the empty string case,quite suits well

The change event works well.. Can i know the difference between the two events when will they be activated..

[Userdd]

Based on the field selected in combo box 1 and value from the combo box 2, we need to display the values from the table.
Can you please suggest some method.. how to query!?