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]

Please upload a copy of the accdb in which you performed this operation.

[#3]

it is not a bug.

if you have Inner Join your tables in the query, Yes it will delete records on both tables.

[#4]

I am not using a delete query.
I am deleting records from the form in datasheet view by selecting records using the record selector on the form and then pressing delete key on keyboard.

[#5]

Unable to see how to upload into a post so I have uploaded the testdb to my OneDrive folder. Link below.
https://1drv.ms/u/s!Avi7rxURRiAr0nujcRMuFri1fw_f?e=47PqwX
The db is read only in OneDrive so please download to test.

I am deleting records from the form in datasheet view by using the record selectors to highlight and select records, then either press keyboard Delete key or use Delete button in Records section of Ribbon Home tab.
If the form Data Property 'Order By' is blank (as at present) then records are deleted just from Table2 as I would expect. However, if you apply a sort to the form by using the arrow next to the column label and then delete records they are deleted from both Table1 and Table2, even though there is no defined relationship between the two tables.

[#6]

I'll try it out later today, but I'm tied up for a few hours, sorry.

However, I see Arnel has joined the discussion; maybe he'll be able to respond quicker.

[#7]

No rush, I am now away for a long weekend. Not back till Tuesday.
Thanks.

[#8]

THis puzzles me. I have to do more research. I can't think of an explanation that explains this. Unfortunately, time is still a bit limited today.

However, I do see that the only control on the sample form is a combo box whose row source is table1. That implies the delete is actually operating against the records in the combo box as well as the form's recordsource. 

[#9]

Again it is not a bug.

If you are using Join in your Recordsource in your Datasheet form, and

joining on Both PK fields (which means it will result in 1=1, relationship. Table2.ID2 Inner/Left/Right Join to Table1.ID1), then it will Delete On Both tables.

SOLUTION:

1. Do not make ID2 of Table2 as PK and Do not make Unique Index to it.

Or 

2. Use Dlookup() as Control Source of your "Description" Column:

=DLookup("Desc", "Table1", "ID = " & Nz([ID2], 0))

[#10]

Arnel. That's pretty much what I thought, too. However, the sample accdb uploaded by the OP does not have a query as a recordsource. Sorting the values in a datasheet view by the value column (unbound, second column) of the combo box causes deleting records in table 2 to delete records in table 1. It looks almost like the sort puts focus on the rowsource of the combo box (table 1) as well as the recordsource of the form (table 2) and the delete then impacts both tables.

I haven't yet had time to explore further, though.

[#11]

@George_Hepworth 

I think we have a valid explanation of this bug, and it does appear to be a bug, albeit a pretty obscure one.

From Ben Clothier:
"See, when you filter or sort something using the datasheet functionality, it will add the combobox's rowsource which must happen because when you sort, you are sorting on the displayed values which is impossible to know from the form's recordsource alone. As a consequence, it will join the combobox's rowsource to the form's recordsource and filter/sort on the columns from the combobox's rowsource. 

So the bug is probably that instead of implicitly doing a DELETE <form's recordsource>.* FROM <form's recordsource> WHERE ...;, it's probably doing DELETE * FROM <form's recordsource> WHERE ...; which would result in the observed behavior. The DELETE * is an Access SQL extension and whenever we join a table in a delete query, we do have to make sure we specify the table rather than using just the asterisk to target the deletions in only that table. "

[#12]

@AndyBentley-7653 

See the previous response for an explanation which I believe accounts for this bug.

Here's an alternative that deletes one record at a time from the form, whether sorted or unsorted. There may be a way to delete all of the table 2 records using a similar technique. You can implement that if you wish.

[#13]

Actually, it turns out that you were on the right track, except the join is implicitly materialized by sorting on the combo box.

[#14]

it's a Dangerous approach on having 2 tables with Same PK values.
Access will not distinguish which PK value belongs to which table.
therefore it will delete both.
also this is not a Normal table design.
there can only be PK and the Other field on the other table should be made FK.
there is no such things as "1=1" like this one, since you can just have 1 table
in this situation.

[#15]

@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.

[#16]

@George_Hepworth 

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

[#17]

@isladogs 

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.

[#18]

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.

[#19]

@isladogs 

@George_Hepworth 

@arnel_gp 

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 requeried

Surely 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?

[#20]

@AndyBentley-7653 

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.