Microsoft Access form - unable to delete records added via append query

Question

I have a simple Access form which has a sub-form based on a linked table.
The linked table is not related to any other table in the database.
There are two controls on the form which allow me to select and add new records.
If I do this manually then I am also able to select and delete these records.

There is also the facility to add multiple records to the form by selecting a type of record from a combo-box.
This causes an append query to be run which adds records to the table and refreshes the form.
If I then try to select and delete any of the records I get an error message saying that the record cannot be deleted or changed because another table in the linked database contains related records, but the table that I am deleting from is not linked to any other tables.

If I open the form in design mode and query the underlying dataset directly then I am able to select and delete records.
I am also able to delete all the records in the table by running a delete query called via a button on the form.

Can anybody offer any clue at all as to why I cannot delete records individually from the form when they have been added via the append query?

I am running Windows 10 Home version 21H1 build 19043.1826
and Access version 2206 Build 16.0.15330.20260 (64-bit)

Answer 1

What appears to have happened is that, when the values of the primary key of tblFTraders were inserted into the TraderID column in tblFBulkTraders the latter table seems somehow to have inherited the referential integrity constraints of the relationships between tblFTraders and other tables in the database. Consequently any attempt to delete those rows from tblFBulkTraders is unsuccessful. This should not happen of course, and is not something I have ever seen before. If the enforcement of referential integrity is removed from all the relationships between tblFTraders and other tables it is possible to delete rows from tblFBulkTraders. An unenforced relationship type serves no useful purpose, however, and to do this would be prejudicial to the integrity of the data.

I can offer no explanation for this bizarre behaviour. I did wonder whether tblFBulkTraders might have been created by copying the structure of tblFTraders and then amending the copy, but the two tables are very dissimilar, so this seems unlikely.

Answer 2

I have done the following with your file, as described in your email:

1. Open Table3 and delete all records
2. Open Table1 – if there are only 2 records (QED) then add some more
3. Open Form1 – click the Add records button – this will copy all the records from Table1 to Table3
4. Apply a sort to the subform datasheet by means of the arrow on the column header
5. Select all records on the subform and then delete them and confirm the deletions.

I can confirm that the behaviour is as you described, with rows being deleted from Table3 and Table1.

However, when I create a new file of the same structure and append rows from Table1 to Table3, then delete rows from Table3 in the datasheet, both with a sort order applied and without, only the rows from Table3 are deleted, as expected. Those in Table1 remain intact, whether or not there is a match in Table2.

I'm using Access version 2207 (build 15427.20210 click-to-run).