Forum Discussion
Deleting records from a form in datasheet view when a sort has been applied to a combo-box.
I agree that the database design isn't appropriate & that this isn't (or at least shouldn't be) an approach that is used in a real life situation
However, I disagree with arnel_gp about this 'cascade delete' being normal behaviour
Deleting records from one table should have no effect on the records from another table UNLESS a relationship has been set with referential integrity and cascade delete. See my article: Relationships1 (isladogs.co.uk)
As the OP stated, deleting records via the form does NOT normally delete records from the other table. That is correct behaviour as there is no relationship between the tables.
Applying a sort causes the records in the unrelated table to also be deleted as he reported.
That is definitely unwanted behaviour and is reasonable to consider it as a bug.
More accurately it is an unwanted side effect that isn't normally an issue in most circumstances
It should be noted that there is still no relationship between the tables
Deleting records from Table2 directly does not affect Table1
The problem is the combo on the form which has a row source to Table1 and the Inherit Value List property is set to Yes (default value). Change it to No as in the screenshot.
Now sort the list and delete records in the form. The records in Table1 are no longer deleted
A similar issue is observed in my Combobox Zombies Puzzle (isladogs.co.uk) example app
Hope that helps
Thanks, Colin.
I had used Jet Show Plan, as Mike Wolfe suggested, without spotting anything useful yet.
Too many irons in the fire to spend a lot of time on this one.
With regard to Inherit Value List, this statement is in the documentation, emphasis added:
"The InheritValueList property is effective only when the combo box is bound to a Lookup field and the RowSourceType property is set to Value List."
The fact that is impacts this Lookup using a table does suggest a bug, since it should only effect a value list. That confuses me, anyway.
Nonetheless, I think Ben's hypothesis is on the right track. Because the form is implicitly joining the two recordsets to accomplish the sort, the delete then impacts both. But, knowing that the InheritValueList property avoids the bug is very helpful.
- Thank you - "Simplifying" point noted.
I think we've found a satisfactory explanation for the behavior and a usable and effective work-around. That work-around is the one Colin offered. It works without having to modify the internals of accdbs. Unfortunately, as we've learned in the last few months, unanticipated regressions are all too often the end result of "simple" changes in the Windows or in Access. I can't see Microsoft putting this one very high on the list of things that could be addressed.
Also, I can't resist offering this link as a partial response to one of the points made in the final post.
- The testdb uploaded was stripped of functionality to the bare minimum in order to illustrate the point
Why "Simplifying" question content can be counter-productive
Pay particular attention to the second point raised, if you will please.
Thank you all for looking at this and I appreciate your comments and suggested workrounds.
I would like to make the following comments, and offer a suggestion as to how the bug can be fixed.- The testdb uploaded was stripped of functionality to the bare minimum in order to illustrate the point
- I was taught that a non-trivial table should always have a primary key.
- In the real life situation where this problem was first noticed Table1 is a table containing Customer Records. Table2 is used to select Customers prior to producing Invoices for the selected Customers. Table2 includes other fields and the CustomerID is used as the primary key on Table2 to prevent duplicate records from being added. Using a combobox to select Customers and add records to Table2 is the obvious way to do this. If the user makes a mistake whilst selecting Customers then it is reasonable for them to be able to delete the record from Table2 before Invoices are created.
- If a record is deleted from a stand-alone table that does not have any defined relationships to other tables in the database then in my opinion there should be no way that records in other tables should ever be affected.
- The ability to sort records in a form datasheet by clicking on the column label is a really useful feature but I think that the way that this is currently implemented is dangerous.
- Applying a sort on a recordset in this way (as well as causing the delete issue) is currently changing the design of the form, by modifying the OrderBy property. This change should only be temporary and should not be saved. There can be reasons why the form has been originally designed with a different default sort order, implemented by means of the OrderBy and OrderByOnLoad properties.
The final point leads to my suggestion as to how to fix this. I am not privy to the inner workings of Access but I assume that currently when a sort is applied via a column label on a form in datasheet view that a routine is run whereby
a) The OrderBy property is modified
b) The form is requeriedSurely all that needs to be done is to modify the routine which does this so that it saves any original value for the form OrderBy property, including if it is blank, and then replace that value, after the requery has been run?
- That's a good point regarding Inherit Value List.
As you say, it SHOULD only affect Value List row sources so it shouldn't apply in this example.
The fact that it does so, confirms it is a bug....though a somewhat obscure bug due to the unusual usage.
