Forum Discussion
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:
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?
- Please upload a copy of the accdb in which you performed this operation.
- 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.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.
it is not a bug.
if you have Inner Join your tables in the query, Yes it will delete records on both tables.
- 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.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))
