[RESOLVED] Selecting Multiple Items in a Listbox

[manofsteel00]

I am having trouble with allowing users to select multiple items in a Listbox and then allowing them to be removed after clicking a "Verify" button. If multiple items were selected, only the first selected item would be removed from the listbox after selecting the "Verify" button. The other selected items would be still highlighted. Any suggestions?

Here's the code for the verify button:

'*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
    Set recordSet = New ADODB.recordSet
    
    Dim counter As Integer
    counter = lstVerificationScreen.ListCount - 1
    Do While counter >= 0
        If (lstVerificationScreen.Selected(counter) = True) Then
 
            Dim selectStatement, sSQL As String
            Dim strArray() As String
            strArray = Split(lstVerificationScreen.List(counter), vbTab, -1, 1)
                               
            selectStatement = "SELECT * " & _
                              "FROM [MAIN] " & _
                              "WHERE ID=" & strArray(0)
            
            sSQL = "UPDATE [ALTER] SET TYPEOFCHANGE='DELETED' " & _
                   "WHERE [MAIN ID]='" & strArray(0) & "'"
            connection.Execute sSQL
            
            sSQL = "UPDATE TOGO AS t SET CHECKED = TRUE" & _
                   "WHERE PROD = '" & strArray(0) & "'"
            
            recordSet.Open selectStatement, connection
            
            If (recordSet.RecordCount = 1) Then
                selectStatement = "DELETE FROM [MAIN] WHERE ID=" & strArray(0)
                connection.Execute selectStatement
            End If
 
            lstVerificationScreen.RemoveItem (counter)
            
            
        End If
        DoEvents
        counter = counter - 1
    Loop
       
    connection.Close
    Set recordSet = Nothing
    Set connection = Nothing
    
End Sub
'*ENDOF*CMDVERIFY_CLICK()******************************************************

Thank you.

[brucevde]

Is the ListBox updated/refreshed from the database after cmdVerify has executed?

Three things I noticed.

The Main.ID field is treated as numeric data, while the Alter.Main_Id and Togo.Prod fields are treated as string data. Is that correct?

If (recordSet.RecordCount = 1) Then
What if RecordCount is > 1 or -1 (which is possible)?

The second update statement is never executed!

[manofsteel00]

How do you update/refresh the data from the database after cmdVerify has executed?

The Main.ID field is numeric; however, the Alter.Main_Id field is not. Togo.Prod field was corrected to be treated like a number. Thanks for pointing that out.

In this statement I'm checking if the data is currently in the database as the database is refreshed daily. So if someone selects an item and counts it that day it will be in the database, but if not it will not be in the database. The data is refreshed daily since there is no primary key on the database where the information comes from thus no way to correctly update the existing data. I unfortunately have no control over that aspect.