You cannot add or change a record because a record is required in header_generator

[KWHAT]

Hey guys ive been looking online and seeing a lot of comments about cascading deletes, Relationships and enforcing referential Integrity. However i still cannot find my error where i have went wrong, hopefully someone out there is quicker than me, by the way im new in Access. So here is a image of my relationships,


So basically i have many tables main table is the Header_Generator which contains HG_ID and links it to all the the other tables, so my main goal was if i deleted a HG_ID it will delete the records related to this field everywhere. Saying that i had created two forms a parent form contains the HG_ID creation and the child form which contains the HEADER_ID, so when im in the parent form and i decide to manual delete this record and redirect to another form completely i get no error message and everything is working as suppose too. However i created a button to this manual step however since i've created this i get the error message i mentioned before

 
 You cannot add or change a record because a record is required in header_generator

if i avoid the message i can see the code does work, its just that error message so annoying, so this is my coding i have in VBA

 
'Delete current data and redirect to home screen.
 Private Sub DeleteEntry_Click()

'Promt
 strPrompt = "All windows created will be delete, are you sure you want to delete this entry?"
'Dialog's Title
 strTitle = "Warning"
'Display MessageBox
 iRet = MsgBox(strPrompt, vbYesNo + vbCritical, strTitle)


If iRet = 6 Then

    Me.USER.SetFocus
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.Requery
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close

    DoCmd.OpenForm "HOME", OpenArgs:=Permissions & "|" & UserName & ""
  Exit Sub

End If
End Sub

Overall i might be a lost case so help a brother out!!! Thank you!

[John_G]

Hi -

Are you able to determine which line of code is generating the error? If I had to guess, I think it might be DoCmd.RunCommand acCmdSaveRecord - I don't quite see why that line is in there - there is nothing new to save since you just did a requery.

The error could be one of two things -

1) You are trying to add a record to one of the tables on the "many" side of a relationship when there is no matching record in Header_Generator or
2) You are trying to delete a record in Header_Generator, while there are still related records in one of the other tables, and Cascade delete is not selected.

What you need to do is look at the properties of all the relationships, and make sure that both the Enforce Referential Integrity and Cascade Delete options are selected for all.

John

[KWHAT]

Hi -

Are you able to determine which line of code is generating the error? If I had to guess, I think it might be DoCmd.RunCommand acCmdSaveRecord - I don't quite see why that line is in there - there is nothing new to save since you just did a requery.

The error could be one of two things -

1) You are trying to add a record to one of the tables on the "many" side of a relationship when there is no matching record in Header_Generator or
2) You are trying to delete a record in Header_Generator, while there are still related records in one of the other tables, and Cascade delete is not selected.

What you need to do is look at the properties of all the relationships, and make sure that both the Enforce Referential Integrity and Cascade Delete options are selected for all.

John

Hey John well I am assuming its number 1, Ive been looking and all are set to 1 to many to this table, the issue im assuming is that in the parent form which contains the child sub form, when i press delete button i created i am deleting the parent form record to which all other tables is the parent so 1 to many relationship (assumption all other tables that have this 1-to-many relationship will delete), the thing i want to note is that there is no error when i delete the entries in the sub form manually then press the delete button in the parent form .

[John_G]

Hi -

"the issue im assuming is that in the parent form which contains the child sub form, when i press delete button i created i am deleting the parent form record to which all other tables is the parent so 1 to many relationship (assumption all other tables that have this 1-to-many relationship will delete)"

This will not happen unless you edit the relationships to enforce referential integrity, and allow cascade deletes - these are not automatically selected when you define the relationship.

"...the thing i want to note is that there is no error when i delete the entries in the sub form manually then press the delete button in the parent form ."


And neither should there be. Deleting entries on the "many" side of a relationship will not violate referential integrity

However, the error you originally described does not come from a deletion. It means you have tried to add a record to the "many" side of a relationship when there is no corresponding record on the "one" side.

For example - customers and orders. The relationship between customers and orders is one-to-many, and if you are enforcing referential integrity and try to enter an order for a non-existant customer, you will get that error.

HTH

John