Clearing and Maintaining Comboboxes VBA (ArcObjects) 6.0

MichelleCouden1 · ‎03-13-2013

I am writing a program to work through 3 comboboxes upon user selections. I am trying to fix the top part which involves populating the year combobox when either the urban or annual stations are selected. My program works but when they select Annual the year should only be 2010 instead it shows all the years for Urban and does not clear. For Example: Annual is selected the year 2010 should show up, Urban is selected then 2010, 2011, 2012 should show up. Right now when they select Annual it shows 2010,2011,2012. Please help!! Remember this is in VBA (ArcObjects) 6.0!!

Private Sub UserForm_Initialize()

    Dim WorkDB As DAO.Database
    Dim workRecSetA As DAO.RecordSet
    Dim workRecSetB As DAO.RecordSet
    Dim x As Integer
    
     If cboStations.Text = "Annual" Then
       cboYear.AddItem "2010"
    End If
    
    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
    Next
       
    
    If cboStations.Text = "Urban" Then
        cboDistrict.Clear
        cboYear.Clear
    End If
    
    

End Sub

Private Sub cmdCancel_Click()

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

    If cboYear.Text = "2010" Then
          cboDistrict.Clear
        cboDistrict.AddItem "Abilene"
        cboDistrict.AddItem "Amarillo"
        cboDistrict.AddItem "Austin"
        cboDistrict.AddItem "San_Antonio"
        cboDistrict.AddItem "Waco"
        cboDistrict.AddItem "Wichita_Falls"
    ElseIf cboYear.Text = "2011" Then
          cboDistrict.Clear
        cboDistrict.AddItem "Beaumont"
        cboDistrict.AddItem "Houston"
    ElseIf cboYear.Text = "2012" Then
          cboDistrict.Clear
        cboDistrict.AddItem "Brownwood"
        cboDistrict.AddItem "Bryan"
        cboDistrict.AddItem "Childress"
        cboDistrict.AddItem "Corpus_Christi"
        cboDistrict.AddItem "El_Paso"
        cboDistrict.AddItem "Lubbock"
        cboDistrict.AddItem "Odessa"
        cboDistrict.AddItem "Yoakum"
                    
    End If

KenBuja · ‎03-13-2013

You'll just have to add in the logic in the cboStations_Change subroutine for the different selections

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

MichelleCouden1 · ‎03-13-2013

I was afraid you were going to say that. So, there would'nt be a way to keep the WorkRecSet codes for the combobox. I was just trying to do that so when people changed the access data tables in the future I would pull up the correct years.

KenBuja · ‎03-13-2013

Are your access tables configured so that you can pull out the correct years for Urban versus Annual? If you can query for the Annual years, then you can put that logic into the cboStations_Change subroutine, just like you have it in the Initialize routine to populate cboDistrict and cboStations