Dynamic select case

[shaneucc]

I want to know if it's possible to make a dynamic select case. In my form I have 3 separate combo boxes. What I want to happen is depending on what was selected in one the options in the other two change and if you select something in the second the option in the third narrows down again. The user can select these in any order. They can use one by itself or all three independently.

In my code I can do this with many select statements, e.g

Select Case Me.combobox1.Value
Case "choice1"
Forms!CurrentForm!combobox2.RowSourceType = "Table/Query"
Forms!CurrentForm!combobox2.RowSource = "SELECT fieldname FROM tablename WHERE fieldname = '" & Me.combobox1.Value & "'"
End Select

In the place I have "choice" is it possible to write something along the lines of,
Me.combobox1.value = "SQL Code"

The idea that this code would still work if the user adds more data to the tables which these combobox choices come from.

Any ideas?

 

[jdraw]

Research Cascading Combo boxes -that's the name for the functionality you are seeking.

See Paul's site for more info
http://www.baldyweb.com/CascadingCombo.htm
or (for free video)
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

These should get you started. Good luck.

 

[shaneucc]

Thanks for the help. I've been using cascading combo boxes elsewhere in the code but I'm having difficulty here because I want the user to be able to select the combo boxes in any order they choose. Any ideas?

 

[gemma-the-husky]

you can't use cascading combo boxes then. the idea of ccbs is that the query that drives each successive ccb is refined by the other cbs.

this hierarchy recognises the organisation of your database - so eg

select a sales REGION first. based on the selected area, you then select a sales COUNTRY. based on this, you get to a local AREA.

So how is your data constructed that the combo boxes are not hierarchical. how can it be that you can select in any order?

 

[shaneucc]

It's tricky. At the moment I'm using a lot of if statements.
Each of the three combo boxes is dependent on what's selected in the other two. It follows the pattern if "All" is selected it will display everything, if a particular value is selected the next combo box displays the corresponding value from the table. This gives each combo box in effect four constraints. Say you're choosing a value in combo box 1 the options are if combo box 2 = all and combo box 3 = all, if combo box 2 = all and combo box 3 = a particular value, if combo box 3 = a particular value and if both combo box 2 and 3 = particular values.
In order to get this to work for each combo box I have if statements nested within other if statements. It's more or less functional, just a bit messy.

 

[nanscombe]

Almost sounds like you want to add further combo boxes to chose the order:

Combo 1: (Country / Region / Area) - Geographic Operator
Combo 1a: All values of option picked - Values

Combo 2: (Country / Region / Area) - Geographic Operator
Combo 2a: Values limited by choices in 1 & 1a - Values

Combo 3: (Country / Region / Area) - Geographic Operator
Combo 3a: Values limited by choices in 1 & 1a, 2 & 2a - Values

That way you would have a set of dynamic, cascading combo boxes

Cascading combo boxes would work for hierachical data but this would work for non-hierachical, or non related, data as well.


If you are having trouble with lots of "If" statements you could try code something like this ...

Private Sub refreshCountries()
Dim strWhere as String, strSql as String

 strWhere =""

 If Len(strWhere) Then strWhere = " WHERE " & strWhere

  strSql = "SELECT ID, Country FROM tblCountries" & strWhere & " ORDER BY Country"

 Me.cboCountry.RowSource = strSql
End Sub

Private Sub refreshRegion()
Dim strWhere as String, strSql as String

 strWhere =""

  If me.CboCountry <> "All" Then
    If Len(strWhere) Then strWhere = strWhere & " AND " 
    strWhere = strWhere & "Country = " & me.cboCountry.Column(0)
  End If

  If Len(strWhere) Then strWhere = " WHERE " & strWhere

  strSql = "SELECT ID, Region FROM tblRegion" & strWhere & " ORDER BY Region"

 Me.cboRegion.RowSource = strSql
End Sub

Private Sub refreshArea()
Dim strWhere as String, strSql as String

 strWhere =""

  If me.CboCountry <> "All" Then
    If Len(strWhere) Then strWhere = strWhere & " AND " 
    strWhere = strWhere & "Country = " & me.cboCountry.Column(0)
  End If

  If me.CboRegion <> "All" Then
    If Len(strWhere) Then strWhere = strWhere & " AND " 
    strWhere = strWhere & "Region = " & me.cboRegion.Column(0)
  End If

  If Len(strWhere) Then strWhere = " WHERE " & strWhere

  strSql = "SELECT ID, Area FROM tblArea" & strWhere & " ORDER BY Area"

 Me.cboArea.RowSource = strSql
End Sub

 

[gemma-the-husky]

the easiest way is to base the combo boxes on queries that take into account the other combos

instead of complicated inline nested ifs, i would have a function that does this. there may still be a lot of ifs, but it will be easier to read and maintain.

then in the after update for each combo, you can just requery the other combos.