从多选列表框中删除多个选定记录(访问) [英] Delete multiple selected record from a multiselect listbox (Access)

问题描述

我需要删除从Access的列表框中选择的多个记录.我有SQL和VBA可以从非多选列表框中删除一个选定的记录，但是我似乎无法适应该代码从多选列表框中删除多个选定的记录.这是当前的代码；它附加到按钮On_Click事件上，并且列表框名为listboxname:

I need to delete multiple records selected from a listbox in Access. I have SQL and VBA to delete one selected record from a non-multiselect listbox, but I can't seem to adapt the code to delete multiple selected records from a multiselect listbox. Here's the code as it stands; it's attached to a button On_Click event and the listbox is called listboxname:

Private Sub DeleteSelected_Click()
    Dim lngID As Long
    Dim strSQL As String

    If IsNull(ListBoxName) Then
        Exit Sub
    End If

    With Me.ListBoxName

    lngID = ListBoxName.Value

    strSQL = "DELETE * FROM [tablename] WHERE [tablefieldname] =" & lngID

    CurrentDb.Execute strSQL

    ListBoxName.Requery

    End With
End Sub

我尝试设置

lngID = ListBoxName.Value

到

lngID = ListBoxName.ItemsSelected(0)

或

lngID = ListBoxName.Columns(0)

无济于事.看来SQL一定有问题，但是我不知道如何解决它.有什么想法吗?

to no avail. It seems like the SQL must be at fault, but I haven't a clue how to fix it. Any ideas?

那太酷了:)欢呼Belial.这是更新的代码

That's very cool :) Cheers Belial. Here is the updated code

    Private Sub DeleteSelected_Click()
    Dim strSQL As String
    Dim vItem As Variant
    Dim strSet As Long

    If IsNull(ListBoxName) Then
        Exit Sub
    End If

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

    strSQL = "DELETE FROM Carers WHERE Carer_ID IN (" & strSet & ")"

    CurrentDb.Execute strSQL

    ListBoxName.Requery

    End Sub

希望我做对了

推荐答案

这是查询中的一个SQL错误:

Here is one SQL error in your query:

strSQL = "DELETE FROM [tablename] WHERE [tablefieldname] =" & lngID

不需要*.

无论如何，这是您可以执行的另一种方法:

Anyway, here is another way you can do:

将选择的项目放入用,逗号分隔的字符串中，然后使用IN运算符一次删除所有内容.

Get select items into a String separated by , comma, then use IN operator to delete all at once.

Dim vItem as Variant
Dim strSet as String
//Loop through the ItemsSelected in the list box    
    With Me.ListBoxName
        For Each vItem In .ItemsSelected
            If Not IsNull(vItem) Then
                strSet = strSet & "," & .ItemData(vItem) 
            End If
        Next
    End With
strSQL = "DELETE FROM [tablename] WHERE [tablefieldname] IN (" & strSet & ")"

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