continuous form - deleting multiple records problem

Dear experts -
I have a continuous form. We have had 2 (painful) instances of users attempting to delete a record.
The form has enabled record selectors - perhaps this is where the problem came from.
In any case - when attempting to delete a single record, somehow the program deleted ALL records (!).
How do I guard against this?
Right now I have code in the On_Delete event, but somehow it seems that this was bypassed.
Should I simply get rid of the record selectors?
Or can I keep them but detect in the On_Delete whether more than one record is selected?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Unless the user selected all records by accident (they pressed Ctrl-A, ...instead of Ctrl-C)
I don't see a way to delete all records "by accident"...

There is an Access option that will "Confirm Record deletes"
(Access Options-->Client Settings-->Confirm...)

You can also use code to key off of the Delete event, but try the setting above first...

terpsichoreAuthor Commented:
I actually was able to replicate the problem - I got a message about "cascading deletes" and it DID delete all records!
These data are too dangerous to leave to any chance - what is the safest way to go about this and ensure no chance of multiple deletions?
Jeffrey CoachmanMIS LiasonCommented:
oK, I read a bit deeper.

"Right now I have code in the On_Delete event, but somehow it seems that this was bypassed.>
...can you post this code?, ...perhaps it is not optimal...

<Should I simply get rid of the record selectors?>
Getting rid of the records selectors alone may not stop a user from deleting records,
They may still just select the record (or all the records) and press the delete key.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

terpsichoreAuthor Commented:
1) I noticed that there were some cascading deletes defined in the Relationships on the back end - perhaps I should remove all these to be save?
2) Here is the code in the on_delete:

Private Sub Form_Delete(Cancel As Integer)

Dim isactualvolume As Boolean
isactualvolume = IIf(IsNull(DLookup("[Expense_ID]", "VendortotalByExpense", "[Expense_ID]=" & Me.txtExpense_ID)), False, True)

If isactualvolume = True Then
    MsgBox "There are vendor actuals associated with this item. It cannot be deleted.", vbCritical
    Cancel = True
    Exit Sub
End If

If Me.chkFullyInvoiced = True Then
    MsgBox "This item is flagged complete and fully invoiced. It cannot be deleted.", vbCritical
    Cancel = True
    Exit Sub
End If

end sub
terpsichoreAuthor Commented:
Another idea: Is it safer to not allow deletions, and add a Delete button?
Jeffrey CoachmanMIS LiasonCommented:
<I noticed that there were some cascading deletes defined in the Relationships on the back end - perhaps I should remove all these to be save?>
Cascade delete will delete the Parent record and all Child records.

So if you select a Customer form a form and delete them, all of their "Orders" will be deleted as well (else you end up with orphaned records)
You could probably debate this, ...but I only turn on this option when doing bulk cleanups (perhaps once every quarter), then turn it off

In your case you just stated that "all records were deleted."
So I am not sure now how this form is setup.
What I took from your post is that the user tried to delete one record (on the form) and ended up deleting al records on the same form...

looking at your delete code now...
terpsichoreAuthor Commented:
yes, you're right. they tried to delete one record on the form and ended up deleting all of them. But I noticed that this 'cascading delete' message before it nuked the other records.
Jeffrey CoachmanMIS LiasonCommented:
I cannot vouch for the function... (or the state of the chkFullyInvoiced, when the code is actually run)
(are you sure the correct boolean value is being generated at the time the code is run?)
(are you sure chkFullyInvoiced is actually updated to TRUE)

Another approach would be to filter out these records....
In other words, ...a user cannot delete what they cannot see.

Also note that there is also a "Before Delete Confirm" event, ...that is also cancel-able
(BeforeDelConfirm) try you code on this event as well, and see if it performs in the way you would like...

Jeffrey CoachmanMIS LiasonCommented:
<yes, you're right. they tried to delete one record on the form and ended up deleting all of them. But I noticed that this 'cascading delete' message before it nuked the other records.

Then if you got this message, then they deleted the main record (Customer), but then all the "Orders" were also deleted,..
The way you stated this, it sounded like they deleted all the "Customesrs"
Please confirm.
Jeffrey CoachmanMIS LiasonCommented:
You cannot turn off deletes for the form, and still use your own custom button to delete.
If "Allow deletions" is OFF, then you cannot delete records at all.

Based on what you have said here, first turn of the cascade deletes and see what happens.

(...Yes, you can use SQL to delete records (regardless of the Allow deletes setting), ...but this introduces more possible issues...)
terpsichoreAuthor Commented:
one other thing: when I move the code to BEFORE DEL CONFIRM, I am getting the error "operation not supported in transactions" (never saw that one before) when I hit this line of code:
isactualvolume = IIf(IsNull(DLookup("[Expense_ID]", "VendortotalByExpense", "[Expense_ID]=" & Me.txtExpense_ID)), False, True)
Jeffrey CoachmanMIS LiasonCommented:
Ok then perhaps that was not the appropriate event...

just thought you could try it, to be sure...
terpsichoreAuthor Commented:
So here is exactly what's happening:
1) Multiple records are selected in the continuous form.
2) I press delete
3) it hits the OnDelete event - which fires CORRECTLY the first time when I confirm the deletion. But then it CHOKES - it cannot find the reference to any controls on the form (these are all NULL (which makes sense, since the record has been delete) - but it is choking since it is trying to delete the rest of the records.
Again - we do NOT want to allow multiple deletes on this form in any case!
Jeffrey CoachmanMIS LiasonCommented:
But again, my feeling is that you should not have records that "can" be deleted, being displayed in the same form/location as records that should "never" be deleted.

So I would filter the form and not show the "undeletable" records.

Then this entire issue would seem to go away.

Remember, Cascade Deletes being on is a good thing, in that it prevents orphaned records.
So turning it off, then forgetting to turn it back on, ...may be just as bad as forgetting to turn it on in the first place.
Jeffrey CoachmanMIS LiasonCommented:
I think our posts are crossing, ..I am just reading your post  now...

hang on...
Jeffrey CoachmanMIS LiasonCommented:
< Multiple records are selected in the continuous form.>
Are these all records that you feel are safe to be deleted? Or are these just records the user selected without regard to whether or not they should be deleted?
If so, then see my post about filtering the form

< Again - we do NOT want to allow multiple deletes on this form in any case!>
Then one slight deterrent would be to use a single view form… (Yes, the user can still select multiple records, but they would have to know that this is possible.)

Again, clearly define “multiple deletes”?
Deleting one main Record and having all the “multiple “child records deleted?
Or selecting/deleting multiple main form records?

I think the focus of this question is shifting….?

This is all difficult to visualize without any context…
Can you post a sample database following these guidelines?

1. Back up your database(s).
2. If the database is split, combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any extraneous records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the Compact/Repair utility.
12. Remove any Passwords, Security and/or login prompts.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words a database that we can easily open and immediately see and/or troubleshoot the issue.
Post the explicit steps to see the issue.
And if applicable, also include a clear, graphical representation of the *Exact* results you are expecting, based on the sample data

terpsichoreAuthor Commented:
Sorry  it is a little hard to send/illustrate.
Let me answer your questions:
1) The records being deleted are those being shown.
2) However, now that you mention it, the record set does include a few fields from other tables - so not sure how the system 'knows' which is the main record being deleted...
3) There are no 'child' records to be deleted
4) distinguishing visual between deletable and nondeletable records is too complex and confusing for the user in this instance
5) Instead of using the delete event, can I make the recordset not allow deletes, but trap for the delete key and write an explicit procedure? This might be safest...
Cascade Delete is a powerful option and should only be used when you understand what its purpose is.  If you don't understand its purpose, you won't know which relationships should be defined with Cascade delete.

When you create a relationship and enforce Referential Integrity, you tell the database engine that you want it to prevent orphan records.  This arises in two situations.  The first is if you delete the "parent" record.  Enforce RI will NOT let you delete a parent record that has ANY child records.  The second is if you try to change the foreign key of the "child" record and the ID you supply does not exist in the parent table.  RI will prevent this.

Cascade Delete extends this concept to tell the database engine that when you delete a "parent" record, you want it to delete all child records.  These child records could be in multiple tables if you have defined more than one relationship with the Cascade Delete option.  When you delete the parent, you see the message regarding the delete cascading.  What that is telling you is that deleting the parent record will also delete child records.  It seems like your situation cannot actually be handled by RI since you want to delete the "parent" and related children ONLY if certain conditions exist.  So you have to decide for yourself whether you want the convenience of Cascade delete and are satisfied that your code in the Delete event accurately enforces your business rules.  If you remove the Cascade Delete option.  Your code would need to change.  It would still do the current checks to determine if there are "live" records that shouldn't be deleted but then if there are none, you would need to create a query that deletes the child records.  Then the db engine will allow the "parent" to be deleted since it no longer has any children.

Typically Cascade Delete is only used on hierarchical relationships such as order-orderdetails.  Customer-customeraddress.  In the case of customer-order, you would almost never specify Cascade Delete since the Delete would cascade to a number of tables and delete a lot of data.  The way this works is you can specify Cascade Delete on some relationships of customer but not all.  So Customer-Addresses is pretty safe to specify Cascade Delete because you didn't specify Cascade Delete on Customer-orders.  So, if you try to delete a customer and that customer has orders, the delete will be prevented in spite of the Cascade Delete on the address table.  So - all relationships must be satisfied or the delete of the parent will not happen.

The problem you are having is very strange.  Cascade Delete NEVER deletes siblings, it only deletes children UNLESS, you have specified a recursive relationship.  A common example of this is an Employee table that includes Supervisor.  The supervisor is an employee so the relationship points back to the same table.  This relationship is also called self-referencing.  You would NEVER, EVER set Cascade Delete on this relationship since deleting a Supervisor would cause all the employees who work for him to also be deleted.  Of course in these days of downsizing, it might be convenient.

So back to your problem.  Are the users so fumble fingered that they select ALL rows rather than just the one or do you actually have a recursive relationship defined.  Rather than posting the entire database, it might be beneficial to just post the Database Window.  Please adjust the size of the tables so that all columns are visible and arrange the relationships with a minimum of overlapping lines to make it easier to see.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
i am blown away by the intelligence and eloquence of this site and of these respondents in particular. The problem definitely has to do with the specific record set and changes that were made recently, since I did not have this problem before. I will remove the cascade delete and enforcement of relational integrity on that table, since that is part of the problem. I will then look at whether I want to use the built-in delete capability, or put in a specific delete button for this table, to avoid any confusion. THANK YOU!
Jeffrey CoachmanMIS LiasonCommented:

If PatHartman has answered your question here, there is no need for you to assign me any points for "effort"...

I was just seeking to understand the issue in a wider scope...

As long as you get the results you are after, we are all happy.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.