[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Deleting multiple records using checkboxes as the record selector

Posted on 2010-01-02
4
Medium Priority
?
832 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:latzo4
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 500 total points
ID: 26162864
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26162894
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
 
LVL 3

Assisted Solution

by:bobby6055
bobby6055 earned 500 total points
ID: 26165349
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
 

Author Closing Comment

by:latzo4
ID: 31672003
Thanks for the help!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question