Error with ComboBox

[sandra45]

Hi, I have one combo box A that will show another combo box B when an item in combo box A is selected by the user. I set the code under combo box A_change(). The code will do the followings:
1.When user clicks one item in combo box A, it will look at the relevant table on the database and get any data related to this item and put it in combo box B;
2.same will apply when user clicks another item in combo box A. The code will need to change items in combo box B in accordance with item selected under combo box A. This means I need to remove any items in combo box B which are there due to previous selection in combo box A before showing the correct items for the new selection. Since I set the resource type to value list, I use removeitem method;
3.The relevant code is:
Dim i As Integer
'cmbSub1 is the combo box B
cmbSub1.Visible = True
i = cmbSub1.ListCount
If i <> 0 Then
For i = 0 To i - 1
cmbSub1.RemoveItem i
Next i
End If

If there is only one item to be removed from combo box B, there is no error generated. However, if there are 4 items to be removed from combo box B, the error always comes out at the time the code needs to remove the second last item, i.e. item no. 3 (i=2). The error message is:
Run-time error '6013':
Unable to remove item.'2' not found in list.

I don't understand why the code can't find the remaining item, they are there. Anybody pls help. Thanks.
BTW, I'm using Access 2002 in OS Win2000Professional.

Regards,
Sandra

 

[Eupher]

You're removing items in the wrong order. For instance, suppose you have 5 items in the list. First time through the loop, you remove item 1 (index 0), now there are 4 items in the list. Second time through the loop, you remove item 2, now there are 3 items in the list. Third time through, you remove item 3, now there are 2 items in the list. Fourth time through the loop, uh-oh, your code says to remove item 4 (index 3) but there is no longer a 4th item, so the code bombs. Try this instead:

Dim i As Integer
For i = Me!cmbSub1.ListCount - 1 To 0 Step -1
    Me!cmbSub1.RemoveItem i
Next i

A more direct route might be just to nuke the combo's Row Source property:

Me!cmbSub1.RowSource = vbNullString

HTH...

Ken S.

 

[Eupher]

p.s. Or directly set the row source property of the combo to the new row source value:

Me!cmbSub1.RowSource = 'semicolon delimited list of values

Ken S.

 

[sandra45]

Hi Ken, thanks a lot. You make it so easy. It's okay now.

Why does the index refer to the wrong item? Is it because the index starts from 0 again in each loop?

Regards,
Sandra

 

[Eupher]

Sandra,
No, it's because in your original code each time through the loop the index number increases, but the list gets smaller. With successive trips through the loop, the index number inevitably becomes larger than the number of items remaining in the list, and the error occurs.

Ken S.

 

[sandra45]

Oh I see, thanks Ken.

Regards,
Sandra