Results 1 to 4 of 4
  1. #1
    KWHAT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    15

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

    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
    Code:
     
     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


    Code:
     
    '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!

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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

  3. #3
    KWHAT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    15
    Quote Originally Posted by John_G View Post
    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 .

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ...related record required...
    By degras in forum Forms
    Replies: 11
    Last Post: 11-03-2011, 12:20 PM
  2. record update when I change view
    By mdpepper1 in forum Forms
    Replies: 1
    Last Post: 11-02-2011, 07:50 AM
  3. on change select record?
    By hlcarlin in forum Programming
    Replies: 2
    Last Post: 07-04-2011, 09:45 AM
  4. Record Count/Change
    By TPH in forum Queries
    Replies: 3
    Last Post: 06-21-2011, 03:19 PM
  5. Allow change to a field on new record only
    By Grooz13 in forum Forms
    Replies: 4
    Last Post: 08-16-2010, 11:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums