Delete Records from a listbox

[joslinmk]

I have a form with a listbox on it and I am trying to make a button that will delete the selected records from a table. The List box is filled from a query. Any help would be very appreciated.

Table to delete records from = tbllinkPersonel_Training
listbox = lstTraining_In

_______________________________________________
Private Sub cmdDelete_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim var As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tbllinkPersonel_Training")

If IsNull(Me.cboTraining) Then
MsgBox "No Class selected...", vbExclamation
Exit Sub
End If

For Each var In Me.lstTraining_In.ItemsSelected
With rs
.Delete
End With
Next

MsgBox "Deleted Successfully...", vbInformation

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Me.lstTraining_In.Requery
Me.lstpersonel.Requery
End Sub
____________________________________________

Thanks, Michael

 

[aleb]

I don't know but you code works absolutely fine. What was the question then ?

 

[Mile-O]

I don't know but you code works absolutely fine

Works fine? It doesn't do anything.

There's a loop that seems to go through the ItemsSelected property of a mulit-select listbox yet the record being deleted is the first one in the recordset over and over again which is not possible.

The approach is wrong. It would be wiser to create a delete Query (using the QueryDef object) by concatenating a string from the ItemsSelected in the listbox.

 

[aleb]

I was confused with this part of the code at the beginning

" If IsNull(Me.cboTraining) Then
MsgBox "No Class selected...", vbExclamation
Exit Sub
End If "

but then I thought that this part was check by joslinmk and there is some combo box that returns proper values so I remarked it, and it worked. Then I changed to:

If lstTraining_In.ItemsSelected.count = o Then
MsgBox "No Class selected...", vbExclamation
Exit Sub
End If

it works absolutely fine again .

 

[aleb]

Oops my fault ... it deletes only one record
Hmmm.

 

[Mile-O]

Dim strSQL As String
Dim var As Variant

strSQL = "DELETE * FROM TableName WHERE FieldName In("


For Each var in ListBox.ItemSelected
    strSQL = strSQL & Me.ListBox.Column(0, var) & ","
Next

strSQL = Left$(strSQL, Len(strSQL) -1) & ");"

If MsgBox("Delete these?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
With DoCmd
    .SetWarnings False
    .RunSQL strSQL
    .SetWarnigns True
End With

 

[joslinmk]

Fixed, Final Code, Thanks for the Help

Thank You for the Help. The code works Great. Here is my Final Code

______________________
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Dim strSQL As String
Dim var As Variant

strSQL = "Delete * FROM tbllinkPersonel_Training WHERE tbllinkPersonel_training.IDPersoneltraining In("

For Each var In Me.lstTraining_In.ItemsSelected
strSQL = strSQL & Me.lstTraining_In.Column(0, var) & ","
Next

strSQL = Left$(strSQL, Len(strSQL) - 1) & ");"

If MsgBox("Delete these?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
With DoCmd
.SetWarnings False
.RunSQL strSQL
DoCmd.SetWarnings True
Me.lstTraining_In.Requery
Me.lstpersonel.Requery
End With

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub

 

[Mile-O]

As long as you understand how it works.

And, next time, when posting code can you please use the [CODE] [/CODE] tags? Thanks.