Aug 25 2022 08:01 AM
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?
Aug 30 2022 08:36 AM
Aug 31 2022 12:41 AM
Aug 31 2022 02:00 AM - edited Aug 31 2022 02:02 AM
You are describing a completely different scenario to the issue outlined in this thread.
In the OP's 'simplified database', there was no link between the 2 tables in the form used for demonstration purposes
What I described was correct. Deleting a record from the combo should have no effect on the other table. The fact that it does is a bug. The workround of setting the combo InheritValueList property to No prevents that unwanted behaviour.
Next I'll discuss your exact scenario.
Create a table and add several records. Clone the table & join by the PK fields as you described.
a) Create a delete query to delete a record from one of the tables. The other table isn't affected UNLESS you do all the following: create a relationship, set referential integrity & set Cascade delete related records to True.
In other words, Access ensures data in related tables isn't deleted unless you specifically tell it to do so
b) Create a select query between the 2 tables and display all the fields from one table. Now manually delete a record from that table. In that specific scenario, Access will delate the corresponding record from the other table whether or not there is a relationship between the tables. That is expected behaviour and not a bug.
That is one of the reasons why end users should NEVER be allowed direct access to queries. Nor should deleting records manually be allowed in forms where tables are joined in the manner described
Hope that clarifies matters
Aug 31 2022 03:19 AM
Aug 31 2022 05:26 AM