Delete records from a table based on selecting in a list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to let users delete (multiple) records from a table that they select
in a list box. I am able to write the outline of the required code, but
honestly don't know how to do the actual deletion. I list my code below, and
wonder if anyone can complete the puzzle for me. Thank you. Code:

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
Dim ctl As Control
Dim rst As Recordset
Dim intCurrentRow As Integer
Set ctl = Me!lstARReportLog
For intCurrentRow = 0 To ctl.ListCount - 1
If ctl.Selected(intCurrentRow) Then

'HERE I don't knnow how to do the actual deletion of that row.

End If
Next intCurrentRow
'Next line is screen refresh
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_cmdDeleteRecord_Click:
Exit Sub

Err_cmdDeleteRecord_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteRecord_Click

End Sub
 
Here's what I came up with on my own. This seems to work, but is it the best
way to write this. Also, the refresh does not work, says is not available
now. Comments appreciated:

strSQL = "SELECT * FROM tlkpARReportLog WHERE ReportIndex = "
For intCurrentRow = 0 To ctl.ListCount - 1
If ctl.Selected(intCurrentRow) Then
strSQLText = strSQL & ctl.Column(0, intCurrentRow) & " ;"
Set rst = dbs.OpenRecordset(strSQLText)
With rst
.Delete
.Close
End With
End If
Next intCurrentRow
'Next line is screen refresh
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer7070
 
Back
Top