Delete from table based on listbox selection

[shabbaranks]

Hi chaps,

Upon the results which populate my list box what I am trying to do is allow the user to select various values from the list box and then hit a button which deletes the values from the table. My code is currently:

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

 If IsNull(Me.TSCorrection_listbx) Then
     Exit Sub
 End If
 lngID = Me.TSCorrection_listbx.Value
 strSQL = "DELETE * FROM TSTable WHERE ID = " & lngID
 CurrentDb.Execute strSQL
 Me.TSCorrection_listbx.Requery
End Sub

But it doesn't delete, infact it doesn't do anything. The list box values are still in the list box and if I look in the table they are still there also.

Any suggestions please? Thanks as always

 

[pr2-eugin]

If there are multiple items, then you should be looping through them to delete? What is the RowSource of the ListBox? Bound Column, Column Count and Column Widths?

 

[CJ_London]

is your listbox set to multiselect?

 

[shabbaranks]

Wowsers, I didn't expect a response so quickly.

The rowsource is set to Me.TSCorrection_listbx.RowSourceType = "table/query"

All columns are in the list box but using the widths Ive hidden the ones which aren't necessary

Me.TSCorrection_listbx.RowSourceType = "table/query"
Me.TSCorrection_listbx.ColumnCount = "13"
Me.TSCorrection_listbx.ColumnWidths = "0cm;3cm;2cm;1cm;0cm;2cm;5cm;0cm;0cm;0cm;0cm;0cm;0cm"
Me.TSCorrection_listbx.RowSource = strSQL

 

[pr2-eugin]

Mind showing us what the RowSource strSQL hides? The reason I ask is, the Delete Query, looks for an ID, I am not sure if that is returned by the ListBox correctly to start with.. Then you need to loop through..

 

[CJ_London]

Then you need to loop through..

Providing the listbox is multiselect, otherwise there is only one row selected

 

[shabbaranks]

Mind showing us what the RowSource strSQL hides? The reason I ask is, the Delete Query, looks for an ID, I am not sure if that is returned by the ListBox correctly to start with.. Then you need to loop through..

The value within ID which is in TSTable is returned as I changed the value from 0cm to 2cm to make sure - is this what you mean?

And it is set to multiselect.

Thanks

 

[pr2-eugin]

Try..

Private Sub Del_TsCorrection_tbox_Click()
    Dim lngIDStr As String, varItem As Variant
    Dim strSQL As String
    
    If Me.TSCorrection_listbx.ItemsSelected.Count Then
        Exit Sub
    End If
    
    With Me.TSCorrection_listbx
        For Each varItem In lst.ItemsSelected
            lngIDStr = lngIDStr & .ItemData(varItem) & ","
        Next
    End With

    lngIDStr = Left(lngIDStr, Len(lngIDStr)-1)
    strSQL = "DELETE * FROM TSTable WHERE ID In (" & lngID & ")"
    CurrentDb.Execute strSQL
    Me.TSCorrection_listbx.Requery
End Sub

 

[shabbaranks]

I had to jig the code a little but it now works

 

[pr2-eugin]

If you have provided exactly what I asked for you "might" not have ended up "jig"-ing the code..

You could help someone else someday by adding the "jig"-ed code to this thread ! Good Luck !

 

[shabbaranks]

If you have provided exactly what I asked for you "might" not have ended up "jig"-ing the code..

You could help someone else someday by adding the "jig"-ed code to this thread ! Good Luck !

Sorry, and yes your right. But the bits I "jigged" were relevant to my names etc also

strSQL = "DELETE * FROM TSTable WHERE ID In (" & lngID & ")"

was missing the str at lngIDstr but that was about all