Deleting records from table in a Continuous Form

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

Answer 1

Challenge 1A has been resolved.

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

Answer 2

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

Answer 3

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.