Deleting multiple records using checkboxes as the record selector

I have a form with a subform.  The subform is set a as a  continuous form.  I'd like to have a check boxes that acts as a record selector within the subform and one delete button in the header.  I want to allow the user to select multiple records using the check box and delete them.

The checkbox is bound to a field "isSelected"  If a record cannot be deleted because there are child records associated then a message box to show which "checked" record(s) has child records that first need to be deleted.


thnx
latzo4Asked:
Who is Participating?
 
Helen FeddemaConnect With a Mentor Commented:
Here is a SQL string that will do what you want; you run it from a command button in the header, substituting your table name (you should Requery the subform afterwards):
Private Sub cmdDeleteSelectedRecords_Click()

   Dim strSQL As String
   Dim strTable As String
   
   strTable = "tblContacts"
   strSQL = "DELETE " & strTable & ".*, IsSelected " _
      & "From " & strTable & " WHERE IsSelected=True;"
   Debug.Print "SQL string: " & strSQL
   DoCmd.RunSQL strSQL
   
End Sub

Open in new window

0
 
Helen FeddemaCommented:
Dealing with possible linked records is more complicated; see the sample database from my Access Archon #143 (on archiving records) for some code you can modify for your needs, substituting a Yes/No message box for the Debug.Print statement before deleting the "many" records.
Archiving--AA-143-.mdb
0
 
bobby6055Connect With a Mentor Commented:
latzo4:
I found code and a sample db at this link that will delete multiple records with multiple Checkoxes.
Simply select the multple boxes you want to delete and clik the button to delete them.

The code that does the job (also from the sample db at the link) is in the code snippet below.
For your convenience, please find a modified sample db attached below:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_23299396.html #21335787
 
Cheers !!!
Bobby

Private Sub btnRemove_Click()
    Dim strFilter As String
    Dim rs As Recordset, rc As Long
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    strFilter = "toBeDeleted=" & True
    Me.Filter = strFilter
    Me.Refresh
    Me.Filter = strFilter
    Me.FilterOn = True
    Set rs = Me.RecordsetClone
    rc = rs.RecordCount
    If rc = 0 Then
        Me.FilterOn = False
        Exit Sub
    End If
    Msg = CStr(rc) & " record/s to be deleted, " & "do you want to continue ?"    ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
    Title = "Delete Selected Records"    ' Define title.
    
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then    ' User chose Yes.
        MyString = "Yes"    ' Perform some action.
        DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord
        
        DoCmd.SetWarnings False
        DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete
        
        DoCmd.SetWarnings True
    Else    ' User chose No.
        MyString = "No"    ' Perform some action.
    End If
    
    Set rs = Nothing
    Me.FilterOn = False
End Sub

Open in new window

Multiple-ChkBox-Delete.mdb
0
 
latzo4Author Commented:
Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.