[RESOLVED] Delete Items from a LsitBox

[manofsteel00]

I'm trying to delete items in a listbox that the user has selected. Currently my code deletes half of the items selected. (If 10 items were selected 5 would be deleted from the listbox, and the other 5 will remain). Any suggestions? Thanks

'*CMDVERIFY_CLICK()************************************************************
'NAME: cmdVerify_Click()
'DESC: Allows the user to verify that they have counted the selected item(s).
Private Sub cmdVerify_Click()
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\cycleCount.mdb"
    connection.Open
    
    Dim counter As Integer
    Do While counter < lstVerificationScreen.ListCount
        If (lstVerificationScreen.Selected(counter) = True) Then
 
            Dim selectStatement, sSQL As String
            Dim strArray() As String
            
            strArray = Split(lstVerificationScreen.List(counter), vbTab, -1, 1)
                               
            selectStatement = "DELETE FROM [MAIN] WHERE ID=" & strArray(0)
            
            sSQL = "UPDATE [ALTER] SET TYPEOFCHANGE='DELETED' WHERE [MAIN ID]='" & strArray(0) & "'"
            
            connection.Execute sSQL
            
            connection.Execute selectStatement
 
            lstVerificationScreen.RemoveItem (counter)
            
            
        End If
        DoEvents
        counter = counter + 1
    Loop
    
    
    connection.Close
    Set connection = Nothing
    
End Sub
'*ENDOF*CMDVERIFY_CLICK()******************************************************

[dglienna]

You have to loop in reverse:

[COLOR=Red]counter = lstVerificationScreen.ListCount
Do While counter > 0
[/COLOR]        If (lstVerificationScreen.Selected(counter) = True) Then
 
            Dim selectStatement, sSQL As String
            Dim strArray() As String
                        strArray = Split(lstVerificationScreen.List(counter), vbTab, -1, 1)
                               
            selectStatement = "DELETE FROM [MAIN] WHERE ID=" & strArray(0)
            
            sSQL = "UPDATE [ALTER] SET TYPEOFCHANGE='DELETED' WHERE [MAIN ID]='" & strArray(0) & "'"
                        connection.Execute sSQL
            
            connection.Execute selectStatement
 
            lstVerificationScreen.RemoveItem (counter)
            
            
        End If
        DoEvents
[COLOR=Red]        counter = counter - 1
[/COLOR]    Loop

[BillBoeBaggins]

Probably need to roll back your counter after each delete. Your iterating through your listbox but your not giving yourself credit for the delete you just did.

Doh! Nothing like repeating what the person above says....

Also, you need to offset your "counter" variable because listboxes are 0 based.

[manofsteel00]

Thanks guys