MS Access-多选列表框从表中删除记录 [英] MS Access - Multi Select Listbox to delete records from table

问题描述

我正在Windows 8上使用Access2010.我正在寻找一种使用多选列表框一次删除表中多个记录的方法.我在StackOverflow上看到了这篇文章，它帮助我入门:

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:

从多选列表框中删除多个选定记录(访问)

我调整了解决方案中的代码以使用表和对象，但是由于某些原因，它仅在选择一条记录时才起作用.如果我选择2条或更多条记录，那么什么也不会发生.谁能看一下并帮助我看看我可能在哪里犯了错误?

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

感谢您的所有帮助，我最终使它开始工作，我认为主要的问题是strSet被声明为long而不是整数.最终它可以正常工作，而不会在with语句中用单引号引起来逗号.

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.

这是最终产品:

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

推荐答案

替换此:

With Me.lstOperationProducts
    For Each vItem In .ItemsSelected
        If Not IsNull(vItem) Then
            strSet = strSet & "," & .ItemData(vItem)
        End If
    Next
End 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)

将是:

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

最后一行需要更改为:

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

编辑:我刚刚看到您还在将变量 strSet 设为LONG.您不能这样做，因为 LONG是整数.您必须将其昏暗为字符串.

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.

这篇关于MS Access-多选列表框从表中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！