none
Deleting records from table in a Continuous Form RRS feed

  • Question

  •   I am having a challenge with this continuous form I have for deleting records.

    1.  I created a checkbox to select the records to be deleted.  This issues are as follows:

    A.  Initial opening of the form, display a 'black box' inside of the checkbox.  Would like for it to be blank or OFF.

    B.  When I check 1 of them, they all become checked.  When I uncheck one, they all become unchecked.  I would like to be able to select any row and not have it check all of them.  I.E. I want to select record 1 & 5 only to be checked, then click on the button the delete button, I created and just delete those 2 records.

    C.  How can I look through all of the records in the form and then for the ones, the user has checked for deletion and then write the SQL to delete the record.  I somehow need to identify the records on that row, so I can use the fields to identify the unique records I need to delete from the database.

      Any direction in assisting me or example code where this is done would be great.  I look forward to hearing from you.  Thanx in advance.

    Jerry

    Sunday, August 12, 2018 2:07 PM

Answers

  • The checkbox will need to be bound to a column of Boolean (Yes/No) data type, named Selected say, in the table, not an unbound control.  You can then select specific rows in the form to be deleted, and via a command button in the form's header or footer delete the selected rows, subject to user confirmation with code like this in its Click event procedure:

        Const SQL_DELETE = "DELETE * FROM [YourTableNameGoesHere] WHERE Selected"
        Const SQL_UPDATE = "UPDATE [YourTableNameGoesHere] SET Selected = FALSE"
        Const MESSAGE_TEXT = "Are you sure you wish to permanently delete the selected records?"

        Me.Dirty = False

        If MsgBox(MESSAGE_TEXT, vbYesNo + vbQuestion, "Confirm Deletion") = vbYes Then
            CurrentDb.Execute SQL_DELETE, dbFailOnError
            Me.Requery
        Else
            CurrentDb.Execute SQL_UPDATE, dbFailOnError
        End If

    To prevent any rows inadvertently being left selected, put the following in the form's Close event procedure:

        CurrentDb.Execute SQL_UPDATE, dbFailOnError

    Ken Sheridan, Stafford, England

    • Marked as answer by ballj_351 Sunday, August 12, 2018 10:56 PM
    Sunday, August 12, 2018 10:54 PM

All replies

  • Challenge 1A has been resolved.

    I just need some help with item 1B & 1C from above.  Thanx in advance.

    Sunday, August 12, 2018 2:49 PM
  • The checkbox will need to be bound to a column of Boolean (Yes/No) data type, named Selected say, in the table, not an unbound control.  You can then select specific rows in the form to be deleted, and via a command button in the form's header or footer delete the selected rows, subject to user confirmation with code like this in its Click event procedure:

        Const SQL_DELETE = "DELETE * FROM [YourTableNameGoesHere] WHERE Selected"
        Const SQL_UPDATE = "UPDATE [YourTableNameGoesHere] SET Selected = FALSE"
        Const MESSAGE_TEXT = "Are you sure you wish to permanently delete the selected records?"

        Me.Dirty = False

        If MsgBox(MESSAGE_TEXT, vbYesNo + vbQuestion, "Confirm Deletion") = vbYes Then
            CurrentDb.Execute SQL_DELETE, dbFailOnError
            Me.Requery
        Else
            CurrentDb.Execute SQL_UPDATE, dbFailOnError
        End If

    To prevent any rows inadvertently being left selected, put the following in the form's Close event procedure:

        CurrentDb.Execute SQL_UPDATE, dbFailOnError

    Ken Sheridan, Stafford, England

    • Marked as answer by ballj_351 Sunday, August 12, 2018 10:56 PM
    Sunday, August 12, 2018 10:54 PM
  • I have done a ton of coding and testing.  I did find out about making it a bound control.  Everything seems to be working and testing just fine.  Thank you for the information.
    Sunday, August 12, 2018 10:57 PM