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

arnel_gp 

"Access will not distinguish which PK value belongs to which table.
therefore it will delete both."

I don't think that's entirely correct, but it does express the problem correctly, I believe.

In an Access query, this is acceptable syntax and it will delete records ONLY from table2, as required.

DELETE Table2.*
FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID2;

In an Access query, this is not acceptable syntax for a delete query and won't execute, precisely because it doesn't specify which table to delete the records from.

DELETE *
FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID2;

I am sure that Ben's explanation is correct with regard to the implicit join created by sorting the values in the combo box. That results in a query that does include both tables (i.e. the recordsource for the form, table2, and the rowsource for the combobox, table1) and therefore deletes all records in that query.

The result must be something like this, although it's not materialized explicitly. Remember sorting the values forces Access to bring in the rowsource (table1) to support that sort 

DELETE Table1.* ,Table2.*

FROM Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID2;

And that query will delete all records from both tables because they are now specified (incorrectly, no doubt) as the form's recordsource.

To me this does qualify as a bug in the way the form's built-in Delete function operates.

Also, I wholeheartedly agree the table design here is not appropriate and is itself part of the problem. In fact, it may well be that this particular flaw hasn't been commonly encountered is precisely because the join on two primary key fields is so unusual.