Forum Discussion

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

Iron Contributor

Aug 26, 2022

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.

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

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

George_Hepworth

Silver Contributor

Aug 27, 2022

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