VBA 6.0, Code to change 3 comboboxes upon selection of each! Called Selection Change!

Question

I am trying to write code to have 3 comboboxes change upon selection of each. For Example: In combobox 1 they choose Urban which populates combobox 2 with 2010 and 2011 which then populates combobox 3 with houston, austin and so on. I am trying to use an If then loop but I am getting the error of "Invalid Qualifier" which I am not understanding because it is valid it's been used throughout the whole script. Any help would be great!

    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 receiving the 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

Michelle, you have been active here long enough to become aware of our markup capabilities: please use them; they are extremely fast and easy. Help appears at the right when you first ask the question and can be accessed through the [faq], too. — whuber, Mar 12 '13 at 21:41
There are parts of this that seem to relate to MS Access, but that is not where the code is running as far as I can tell. Where are you running the code? — Fionnuala, Mar 13 '13 at 00:36

score 1 · Answer 1 · answered Mar 13 '13 at 02:12

Your line

cboYear.AddItem "2010", "2011", "2012"

isn't valid. Have a look at the MSDN docs for manipulating combo boxes.

.AddItem takes one or two arguments - the first is the item, the second is a number that indicates where to insert the item. I would hazard a guess that it's converting '2011' into a number, trying to insert it at position 2011 (which doesn't exist of course, because you don't have 2000+ items in your combo box!) and throwing a wobbly.

Try splitting it up:

cboYear.AddItem "2010"
cboYear.AddItem "2011"
cboYear.AddItem "2012"

VBA 6.0, Code to change 3 comboboxes upon selection of each! Called Selection Change!

1 Answers1