Script for populating comboboxes upon selection of each. Called Selection Change!

[MichelleCouden1]

I am writing a script in VBA (ArcObjects) 6.0 for 3 comboboxes to communicate. For Example: When the user selects Urban from the first combobox the second combobox populates with the years 2010 and 2011, the user then selects a year from the second combobox and the third combobox is populated with Austin and Houston. I am trying to use If then loop but I am getting the error of "Invalid Qualifier" on my cboYear box which I am not understanding because it is used thoughout the whole script. My code is below.

Private Sub UserForm_Initialize()           cboStations.Value = "Annual"     cboYear.Value = "2012"          Dim WorkDB As DAO.Database     Dim workRecSetA As DAO.RecordSet     Dim workRecSetB As DAO.RecordSet     Dim x As Integer                    Set WorkDB = DBEngine.OpenDatabase("K:\TASS\2 - GEO-DATA PROCESSING SUPPORT\MICHELLE'S WORK_ENTER NOT!!\Work Folder\Map Automation Project\Access Tables\Map_Automation.mdb")     Set workRecSetA = WorkDB.OpenRecordset(Name:="select * from Districts order by District_Name", Type:=dbOpenDynaset)     Do Until workRecSetA.EOF         cboDistrict.AddItem workRecSetA("District_Name")         workRecSetA.MoveNext     Loop     Set workRecSetB = WorkDB.OpenRecordset(Name:="select * from Stations order by Station_Name", Type:=dbOpenDynaset)     Do Until workRecSetB.EOF         cboStations.AddItem workRecSetB("Station_Name")         workRecSetB.MoveNext     Loop          For x = 2010 To 2015         cboYear.AddItem x     Next            End Sub  Private Sub cmdCancel_Click()      frmMapSetUp.Hide      End Sub Private Sub cboStations_Change()          Dim cboYear As String          If cboStations.Text = "Urban" Then       cboYear.AddItem "2010", "2011", "2012"     >Here is where I am getting the invalid qualifier error!!                                    End If                   End Sub Private Sub cboYear_Change()      Dim cboDistrict As String          If cboYear.Text = "2010" Then         cboDistrict.AddItem "Abilene", "Amarillo", "Austin", "San_Antonio", "Waco", "Wichita_Falls"     Else         cboYear.Text = "2011"         cboDistrict.AddItem "Beaumont", "Houston"                      Else         cboYear.Text = "2012"         cboDistrict.AddItem "Brownwood", "Bryan", "Childress", "Corpus_Christi", "El_Paso", Lubbock, "Odessa", "Yoakum"     End If                       End Sub

[KenBuja · Accepted solution]

In your subroutine Private Sub cboStations_Change, you're setting the variable cboYear to a string. Inside this subroutine this variable is taking precedence over the combobox cboYear. You'll want to do something like this (untested, since I don't have VBA on my machine, however). You should also clear the combobox so that you don't add duplicate values every time the subroutine runs

Private Sub cboStations_Change()     cboYear.Clear     If cboStations.Text = "Urban" Then       cboYear.AddItem "2010"       cboYear.AddItem "2011"       cboYear.AddItem "2012"     End If  End Sub

In addition, the if..then syntax in your next subroutine is incorrect

Private Sub cboYear_Change()     cboDistrict.Clear     If cboYear.Text = "2010" Then         cboDistrict.AddItem "Abilene"         'add the rest     Elseif cboYear.Text = "2011" Then         cboDistrict.AddItem "Beaumont"          'add the rest           Elseif cboYear.Text = "2012" Then 'or just Else         cboDistrict.AddItem "Brownwood"          'add the rest     End If                       End Sub

Another way to add different items to the combobox is through an array. Your second subroutine could be written like this

Private Sub cboYear_Change()     dim myArray() as String     cboDistrict.Clear     If cboYear.Text = "2010" Then         myArray = Array ("Abilene", "Amarillo", "Austin", "San_Antonio", "Waco", "Wichita_Falls" )     Elseif cboYear.Text = "2011" Then         myArray = Array ("Beaumont", "Houston")     Elseif cboYear.Text = "2012" Then 'or just Else         myArray = Array ("Brownwood", "Bryan", "Childress", "Corpus_Christi", "El_Paso", Lubbock, "Odessa", "Yoakum")     End If           For Each myElement in myArray         cboYear.AddItem myElement     Next      End Sub

[MichelleCouden1]

You Rock!! It works perfectly. Thank You bunches!!

[LeoDonahue]

You should probably mark Ken's reply as the answer, not your own.  Just sayin..