Delete record deletes other records (1 Viewer)

I have something very bizarre happening that I cannot figure out.

I have a form with a subform on it. These are not linked. On the first form is one combo box, the records in the subform are requeried every time the combo box changes with the criteria for the subform based on the selection in the combo box.

I haven't got these linked because I also have an Edit option group selection on the main form which changes the subform from Data Entry = False and AllowAdditions = False to Data Entry = True and AllowAdditions = True. This allows me to use the form to enter new records or to edit existing ones by changing the edit mode.

I also have a delete button on this form with the generic code to delete a record:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

The record source for this subform is a primary table on the one side of a one to many relationship.

When I am in the new record mode (Data Entry = True and AllowAdditions = True) and I delete the record I am currently entering it works as expected.

When I am in edit mode (Data Entry = False and AllowAdditions = False ) and I delete the record I am on. It deletes the record but any record in the many side (the secondary table with the foreign key) is also deleted as well. Now I'd expect it to delete the foreign key on the many side and wipe the field but this actually deletes the whole record the foreign key is in from the secondary table.

I have checked my relationship set up for these tables and they are fine:

One To Many Relationship
Referential Inegrity Checked
Cascade Update Records Checked
Cascade Delete Records Checked

I get the usual access warning telling me it is about to delete one record. usually you would also expect the warning to include the comment about this deletion will also delete any related records in other tables, but I don't get that.

I cannot for the life of me figure out why the whole record from the secondary table is being deleted and not just the related field.

Does anyone have any ideas?

I hope I have explained it properly. (Tis always hard to put into words a problem)

Thanks in advance,

Daz

Cascade Delete is what I think is doing it

If you want to dissassociate "many-side" records from their "one-side" record, you need to do it from the "many-side" view. Deleting the "one-side" record will delete the related rows from the "many-side" table because orphans are not allowed. If you have "many-side" rows that you don't want associated with any "one-side" row, you need to go in from the "many-side" and make the foreign key null. That will break the relationship. However, if this is actually your situation, then the "one-side" table is technically a lookup table rather than a parent table and in that case, RI should still be enforced but Cascade Delete should not. The result of that will be that if you try to delete a row from the "one-side" table, you will ONLY be able to do so if there are NO related records in the "many-side" table. A good example is an address table linked to a state table. You don't ever want to delete all the addresses in Connecticut if someone makes the mistake of trying to delete the Connecticut record. However, you may have an address record that for some reason you don't know its state and so you decide to allow stateID to be null.