Filter a Drop down from a drop down box

[nathanh]

Hey all, I am looking for help on creating a after update to filter another drop down box. I am looking to have a drop down box based on a table and from this drop down box filter another drop down box based on a seperate table.


Thank You.


Access 2003
Windows XP

[questionit]

Hey all, I am looking for help on creating a after update to filter another drop down box. I am looking to have a drop down box based on a table and from this drop down box filter another drop down box based on a seperate table.


Thank You.

Access 2003
Windows XP

Hi

It is very easy to do.

You can have write sql statement that will query the table and you can

use the result of sql to filter another drop-down menu.

In Access, you can set source for drop-down menus.
To get what you are trying to do - you can set a default sql for your drop-down menu(s). And when you select an item in one of your drop-down menu, execute your sql to update/filter source of the other drop-down menu.

Hope it helps.

Qi

[nathanh]

Hi

It is very easy to do.

You can have write sql statement that will query the table and you can

use the result of sql to filter another drop-down menu.

In Access, you can set source for drop-down menus.
To get what you are trying to do - you can set a default sql for your drop-down menu(s). And when you select an item in one of your drop-down menu, execute your sql to update/filter source of the other drop-down menu.

Hope it helps.

Qi

Hey Qi, is there any chance that you could post a example for me.

Thank You.

[questionit]

Hey Qi, is there any chance that you could post a example for me.

Thank You.

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
[code=vb]
Private Sub Combo1_BeforeUp date(Cancel As Integer)
Dim sql As String

If Me.Combo1.Value = "April" Then
sql = "SELECT Table1.amount FROM Table1 WHERE (((Table1.amoun t)<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.RowSo urce = sql

[/code]

Hope it helps
Qi

[nathanh]

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
[code=vb]
Private Sub Combo1_BeforeUp date(Cancel As Integer)
Dim sql As String

If Me.Combo1.Value = "April" Then
sql = "SELECT Table1.amount FROM Table1 WHERE (((Table1.amoun t)<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.RowSo urce = sql

[/code]

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_BeforeUpda te(Cancel As Integer)
Dim sql As String

If Me.TYPE.Value = "MISCONDUCT " Then
sql = "SELECT STATDISCRP.MISC ONDUCT FROM STATDISCRP;"

Else If Me.TYPE.Value = "TRANSITION " Then
sql = "SELECT STATDISCRP.TRAN SITION FROM STATDISCRP;"

Else If Me.TYPE.Value = "ADSEP" Then
sql = "SELECT STATDISCRP.ADSE P FROM STATDISCRP;"

Else If Me.TYPE.Value = "DISABILITY " Then
sql = "SELECT STATDISCRP.DISA BILITY FROM STATDISCRP;"

Else If Me.TYPE.Value = "MENTAL_HEA LTH" Then
sql = "SELECT STATDISCRP.MENT AL_HEALTH FROM STATDISCRP;"


End If


// here you filter your other Drop-down menu by changing its Row Source using sql
Me.Combo2.RowSo urce = sql