MS Access - Multi Select Listbox to delete records

问题:

I am using Access 2010 on Windows 8. I was looking for a way to use a multi select listbox to delete several records from a table at once. I came accross this post on StackOverflow and it helped get me started:

Delete multiple selected record from a multiselect listbox (Access)

I adjusted the code in the solution to work with my tables and objects but for some reason it only works when one record is selected. If I select 2 or more records then nothing happens. Can anyone take a look and help help me see where I might have made a mistake?

Private Sub cmdRemoveProducts_Click()

    Dim strSQL      As String
    Dim vItem       As Variant
    Dim strSet      As Long


    'If IsNull(lstOperationProducts) Then
        'Exit Sub
    'End If

    With Me.lstOperationProducts
        For Each vItem In .ItemsSelected
            If Not IsNull(vItem) Then
                strSet = strSet & "," & .ItemData(vItem)
            End If
        Next
    End With

    strSQL = "DELETE FROM tblOperationProductMM WHERE OpProdID IN (" & strSet & ")"

    CurrentDb.Execute strSQL

    lstProducts.Requery
    lstOperationProducts.Requery


End Sub

EDIT:

Thanks for all your help, I ended up getting it to work, I think that the main problem was that strSet was declared as long instead of interger. It ended up working ok without surrounding the comma in the with statement with single quotes.

This the final product:

Private Sub cmdRemoveProducts_Click()

    Dim strSQL      As String
    Dim vItem       As Variant
    Dim strSet      As String
    Dim i           As Long


    'If IsNull(lstOperationProducts) Then
        'Exit Sub
    'End If

    strSet = ""

    With Me.lstOperationProducts
        For Each vItem In .ItemsSelected
            If Not IsNull(vItem) Then
                strSet = strSet & "," & .ItemData(vItem)
            End If
        Next
    End With

    ' Remove the first comma
    strSet = Mid(Trim(strSet), 2, Len(strSet) - 1)

    strSQL = "DELETE FROM tblOperationProductMM WHERE OpProdID IN (" & strSet & ")"

    CurrentDb.Execute strSQL



        For i = 0 To lstProducts.ListCount - 1
        lstProducts.Selected(i) = False
    Next

        For i = 0 To lstOperationProducts.ListCount - 1
        lstOperationProducts.Selected(i) = False
    Next

    lstProducts.Requery
    lstOperationProducts.Requery

回答1:

Replace this:

With Me.lstOperationProducts
    For Each vItem In .ItemsSelected
        If Not IsNull(vItem) Then
            strSet = strSet & "," & .ItemData(vItem)
        End If
    Next
End With

with:

strSet = ""

With Me.lstOperationProducts
    For Each vItem In .ItemsSelected
        If Not IsNull(vItem) Then
            strSet = strSet & "," & .ItemData(vItem)
        End If
    Next
End With

' Remove the first comma
strSet = Mid(Trim(strSet), 2, Len(strSet) - 1)

Also, remember that if the item in question is Text, you will need to surround it with single quotes. So this line:

            strSet = strSet & "," & .ItemData(vItem)

Would be:

            strSet = strSet & "','" & .ItemData(vItem)

And the last line would need to be changed to:

strSet = Mid(Trim(strSet), 3, Len(strSet) - 2) & "'"

EDIT: I just saw you're also Diming the variable strSet as LONG. You can't do that, because LONG is an Integer. You have to Dim it as a String.