This is an example :
1- Create 2 Combo-Boxes (Say Combo1 and Combo2)
2- Set default values of your both Drop-down menus by setting Row Source of your drop-down menus in the property box, seperately e.g: SELECT amount FROM table1 WHERE amount > 1000.
Note: It is not neccessary to set default values of drop-down menus using sql, you can also write a value list instead.
You can set Row Source of each drop-down menu similarly with any sql query.
Now you want to update/filter Combo2 on selecting a value in Combo1.
3- Write simple code
-
Private Sub Combo1_BeforeUpdate(Cancel As Integer)
-
Dim sql As String
-
-
If Me.Combo1.Value = "April" Then
-
sql = "SELECT Table1.amount FROM Table1 WHERE (((Table1.amount)<100));"
-
-
Else If Me.Combo1.Value = "March" Then
-
sql = "SELECT Table1.amount FROM Table1 WHERE Table1.amount)>100));"
-
End If
-
-
// here you filter your other Drop-down menu by changing its Row Source using sql
-
Me.Combo2.RowSource = sql
-
-
Hope it helps
Qi
here is what i came up with from you example but it is giving me a error. Thank you.
Private Sub TYPE_BeforeUpdate(Cancel As Integer)
Dim sql As String
If Me.TYPE.Value = "MISCONDUCT" Then
sql = "SELECT STATDISCRP.MISCONDUCT FROM STATDISCRP;"
Else If Me.TYPE.Value = "TRANSITION" Then
sql = "SELECT STATDISCRP.TRANSITION FROM STATDISCRP;"
Else If Me.TYPE.Value = "ADSEP" Then
sql = "SELECT STATDISCRP.ADSEP FROM STATDISCRP;"
Else If Me.TYPE.Value = "DISABILITY" Then
sql = "SELECT STATDISCRP.DISABILITY FROM STATDISCRP;"
Else If Me.TYPE.Value = "MENTAL_HEALTH" Then
sql = "SELECT STATDISCRP.MENTAL_HEALTH FROM STATDISCRP;"
End If
// here you filter your other Drop-down menu by changing its Row Source using sql
Me.Combo2.RowSource = sql
6
