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,