case statement in a combobox

[enrico via AccessMonster.com]

can you use a case statement on a combobox? for example, i have 2 comboboxes
(combobox 1 and combobox 2)

if the value of combobox 1 is "new york" then values of combobox 2 are "a, b,
c, etc."
if the value of combobox 1 is "new jersey" then values of combobox 2 are "x,
y, z, etc."

how will i state it as a select statement?

 

[John W. Vinson]

can you use a case statement on a combobox? for example, i have 2 comboboxes
(combobox 1 and combobox 2)

if the value of combobox 1 is "new york" then values of combobox 2 are "a, b,
c, etc."
if the value of combobox 1 is "new jersey" then values of combobox 2 are "x,
y, z, etc."

how will i state it as a select statement?

Not a Case, but what you can do is base the second combo box on a query
referencing the first combo box. You'll need a table with (at least) two
fields - the values of Combobox 1 in one column, and the corresponding values
for the second combo in the other.

The rowsource of the first combo can be either (in this example) your States
table, or a query such as

SELECT DISTINCT firstcolumn FROM table ORDER BY firstcolumn;

The second combo would use

=Forms!NameOfForm!NameOfFirstCombo

as a criterion on the first column, and would display the value from the
second column.

You'll need to Requery the second combo in the AfterUpdate event of the first.

 

[enrico via AccessMonster.com]

i tried what you suggested step by step. but my second combo box returns the
same value on what you selected on the first combo box

 

[June7 via AccessMonster.com]

I use the AfterUpdate event for combo box 1. Code modifies combo box 2
RowSource query based on choice of combo box 1 using Select Case as the
decision structure. Use 'combobox2.Requery' after the Select decision. The
RowSource list for each combo box can be either from any table (not
necessarily the same one) or if few items a Value list. Just depends on the
query statement you send to combo box 2 RowSource property. Be sure to set
the combo box 2 SourceType to fit the RowSource.

Select Case me.combobox1
Case 1
combobox2.RowSource = "SELECT fieldname From tablename
Case 2
combobox2.RowSource = "Female;Male"
combobox2.SourceType = "ValueList"
....
End Select
me.combobox2.requery

 

[Douglas J. Steele]

<picky>

It's RowSourceType, not SourceType. And since you're setting it in one case,
you need to reset it in the other case. Note, too, that it's Value List,
with a space.

Select Case me.combobox1
Case 1
combobox2.RowSource = "SELECT fieldname From tablename
combobox2.RowSourceType = "Table/Query"
Case 2
combobox2.RowSource = "Female;Male"
combobox2.RowSourceType = "Value List"
....
End Select
me.combobox2.requery

Technically, the .requery isn't necessary since you've reset the RowSource.

</picky

 

[John W. Vinson]

i tried what you suggested step by step. but my second combo box returns the
same value on what you selected on the first combo box

Please post a description of your table and the SQL view of the query that
you're using for the second combo.