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
if you build a query joing the two table on their PK fields.
and you delete from 1 table, what do you think will happen.
records from both tables will get deleted! Why?
Access will not distinguished which Table you want to delete from since the 2 tables
have exactly same Primary Key.
You can confirm it by making same table as with the OP and linking Table2 to table1 to show the Desc field.
- Already reported by George several days ago
- ok you report it.
i tested it on A2007 (same setup as the OP) and it Never delete records in Table1. You are describing a completely different scenario to the issue outlined in this thread.
In the OP's 'simplified database', there was no link between the 2 tables in the form used for demonstration purposes
What I described was correct. Deleting a record from the combo should have no effect on the other table. The fact that it does is a bug. The workround of setting the combo InheritValueList property to No prevents that unwanted behaviour.
Next I'll discuss your exact scenario.
Create a table and add several records. Clone the table & join by the PK fields as you described.
a) Create a delete query to delete a record from one of the tables. The other table isn't affected UNLESS you do all the following: create a relationship, set referential integrity & set Cascade delete related records to True.
In other words, Access ensures data in related tables isn't deleted unless you specifically tell it to do so
b) Create a select query between the 2 tables and display all the fields from one table. Now manually delete a record from that table. In that specific scenario, Access will delate the corresponding record from the other table whether or not there is a relationship between the tables. That is expected behaviour and not a bug.
That is one of the reasons why end users should NEVER be allowed direct access to queries. Nor should deleting records manually be allowed in forms where tables are joined in the manner described
Hope that clarifies matters