Forum Discussion
Deleting records from a form in datasheet view when a sort has been applied to a combo-box.
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 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))
- 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.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. "
- Actually, it turns out that you were on the right track, except the join is implicitly materialized by sorting on the combo box.
- 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.
