Forum Discussion

AndyBentley-7653's avatar
AndyBentley-7653
Copper Contributor
Aug 25, 2022

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

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: ...
2016
access
developer
George_Hepworth's avatar
George_Hepworth
Silver Contributor
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.
George_Hepworth's avatar
George_Hepworth
Silver Contributor
Aug 27, 2022

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

Resources