Deleting records from a form in datasheet view when a sort has been applied to a combo-box.

[AndyBentley-7653]

I believe that I have found a bug in Access.

I am currently running Microsoft® Access® for Microsoft 365 MSO (Version 2207 Build 16.0.15427.20182) 64-bit .

To reproduce this bug do the following:

1. Create a new empty database.

2. Create Table1 with just 2 fields, 'ID1' - Autonumber (set as Primary Key) and a ShortText ('Desc').
3. Create Table2 with just 1 field, 'ID2' - Long (set as Primary key).

4. Populate Table1 with records.

5. Create and run an append query to copy all records from Table1 to Table2, ID1 > ID2.

6. Create a form based on Table2, and add a combo box control for field ID2 that looks up and displays 'Desc' from Table1.

7. View the form in datasheet view and apply a sort via the arrow on the column label.

8. Select and delete all records.

On my system, and 3 other computers on which I have tried this, this results in all the records also being deleted from Table1!

Is anybody else able to confirm this, or is it just me? 

[#2]

Thank you - "Simplifying" point noted.

[#3]

you can disagree if you like.
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.

[#4]

@arnel_gp 

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

[#5]

ok you report it.
i tested it on A2007 (same setup as the OP) and it Never delete records in Table1.

[#6]

Already reported by George several days ago