Delete record button from table using listbox

[Ruegen]

I have - this is to delete the items listed on the listbox

the idea is the user selects from the listbox and it deletes whatever production in that list is selected.

Never tried this before and not sure why it is not working

Private Sub btnRemove_Click()
Dim rs As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM tblTheatre;")


If Me.listShows.ListIndex = -1 Then
MsgBox "You need to select a production first from the list of shows below in order to delete one from it."
rs.Close
Set rs = Nothing
Exit Sub
End If


If rs.RecordCount = 0 Then
MsgBox "No records to delete."
rs.Close
Set rs = Nothing
Exit Sub
End If


rs.MoveFirst
With rs
Do Until rs.EOF
        Debug.Print rs!ID
        Debug.Print Me.listShows.Column(0)
        If rs!ID = Me.listShows.Column(0) Then
            rs.Edit
            rs.Delete
            rs.Update
        End If
        rs.MoveNext
    Loop
   
End With
 rs.Close
Set rs = Nothing


Me.listShows.Requery
MsgBox "You have removed a production from the list successfully"


End Sub

[pbaldy]

For starters, you need to loop the selected items of the listbox. I would just execute SQL:

CurrentDb.Execute "DELETE * FROM TableName WHERE ID = " & ListboxReference

But you'll need to use the variable from looping the selected items.

[Ruegen]

For starters, you need to loop the selected items of the listbox. I would just execute SQL:

CurrentDb.Execute "DELETE * FROM TableName WHERE ID = " & ListboxReference

But you'll need to use the variable from looping the selected items.

I thought about doing that however I still want to know how to do it the other way....

[pbaldy]

The recordset may work, though the repeated looping of the entire table would be very inefficient. In any case, the first thing that has to change is looping the selected items and using that variable. With the commonly used variable:

Me.listShows.ItemData(varItem)

[Ruegen]

The recordset may work, though the repeated looping of the entire table would be very inefficient. In any case, the first thing that has to change is looping the selected items and using that variable. With the commonly used variable:

Me.listShows.ItemData(varItem)

Thanks pbaldy

[pbaldy]

No problem.