0

I have a listbox that is bound to a table (tblTransferDetail). The Primary Key is 'TransNum' (type INT) and the listbox is bound to this as the first column.

The double click event of the listbox (lstScannedItems) executes the below code:

CurrentDb.Execute "DELETE FROM tblTransferDetail WHERE TransNum = " & Me.lstScannedItems, dbSeeChanges

However, when I double click on a record in the listbox I keep receiving 'VBA Runtime error 3075' and the above line is highlighted.

Where am I going wrong?

Michael
  • 2,507
  • 8
  • 35
  • 71
  • 1
    while in debug mode, what does `Me.lstScannedItems` evaluate to ? (just hover over it) – iDevlop Aug 06 '18 at 09:11
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Aug 06 '18 at 09:18
  • `Debug.Print "DELETE FROM tblTransferDetail WHERE TransNum = " & Me.lstScannedItems` to see the query string. You can see what wrong with the query. – nagarajannd Aug 06 '18 at 09:21
  • In Debug it evaluates to NULL - why is this? – Michael Aug 06 '18 at 09:28
  • You may consider adding a `*` and try - `DELETE * FROM tblTransferDetail WHERE TransNum = " & Me.lstScannedItems` – Vityata Aug 06 '18 at 09:43
  • 1
    We can't tell you why something evaluates to null without knowing how it's set up. Is that box multiselect? Because then you might need to iterate `.SelectedItems` – Erik A Aug 06 '18 at 09:48
  • @Vityata - tried added * but receive runtime error 3075 stll – Michael Aug 06 '18 at 10:55
  • @ErikvonAsmuth It is multi selected - added .SelectedItems but receive error 'Method or data member not found' – Michael Aug 06 '18 at 10:57
  • @Michael Oh, silly, it's `ItemsSelected`, I've added an answer on how to use that. – Erik A Aug 06 '18 at 11:03
  • Just a note, using DoubleClick event on a multi-select listbox is a weird user interface, that will probably not work as you want. – Andre Aug 06 '18 at 11:53
  • @Andre ok thanks for the tip – Michael Aug 06 '18 at 12:49

2 Answers2

1

You could simply iterate through .ItemsSelected and then execute the query with the itemdata for each selected item.

Dim v As Variant
For Each v In Me.lstScannedItems.ItemsSelected
    CurrentDb.Execute "DELETE FROM tblTransferDetail WHERE TransNum = " & Me.lstScannedItems.ItemData(v)
Next
Erik A
  • 31,639
  • 12
  • 42
  • 67
0

Me.lstScannedItems will work only when the value is bound column. if you need to use the selection and then go through the selection collection to find out which one is selected.

Dim opValue As String
Dim x As Long
With Me.lstScannedItems
    For x = 0 To .ListCount
        If .Selected(x) = True Then
            opValue = .List(x)
            Exit For
        End If
    Next x
End With
CurrentDb.Execute "DELETE FROM tblTransferDetail WHERE TransNum = " & opValue, dbSeeChanges
nagarajannd
  • 715
  • 5
  • 11