Results 1 to 5 of 5
  1. #1
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49

    Append Key Violations

    Hello,



    I have setup two tables with a one to many relationship. The ID number one the parent table matches many of the "Connection ID" field I created. After appending a record to the first table, it has an ID of "1" let's say. Next I run another append query for the child table where I am trying to populate the "Connection ID" column with the ID that matches.

    If I take the ID field out of the append, it will accept the records, but otherwise says there are "key violations". However the field I am appending to is not setup as an index. The only thing different about it is that it is identified in the relationship as needing to match the parent ID.

    What am I doing wrong? Thanks for your help!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The child ID field should be a LONG. (If parent is auto)
    the relationship join should be such that will allow many records. (Some do not)
    the id field should allow duplicates.

  3. #3
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    I will check tomorrow to see if I chose a long or not. What is the default for a number field. I probably left it as the default.

  4. #4
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Quote Originally Posted by Chad Access View Post
    I will check tomorrow to see if I chose a long or not. What is the default for a number field. I probably left it as the default.
    Click image for larger version. 

Name:	Access Help.jpg 
Views:	10 
Size:	49.3 KB 
ID:	28442
    It looks like the field is already set to a long. You can see the relationship I have established between "Issue Header" (parent) and "Sales Order Header" (child). I think I have it setup correctly, so I am still unsure why I am getting key violations.


    UPDATE: Just tried to manually add to the child table. It says I cannot add a record because a related record is required in "Issue Header" (parent). Well, I am trying to add a record that has an existing ID number, so why won't it allow this to happen. What step am I missing?

    UPDATE 2 (SOLVED): There was a table that I had been using which was no longer on the relationships screen. However, Access somehow still had a relationship established between that table and another and the append was not satisfying the condition. I solved one!

    Thanks!

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02........

    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names (field names, query names, form names, report names).

    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.


    Having the field name "ID" is poor programming practice. How confusing would it be to have 12 tables with the PF field named "ID" when creating complex queries???

    For the primary key fields, I would use "SalesHeaderID_PK", "IssueHeaderID_PK" and "IssueNoteID_PK" (note the "_PK" suffix)
    For the foreign key fields, in table "LineItemDetail" I would use "SalesHeaderID_FK"
    and in tables "SalesOrderHeader" and "IssueNotes", I would use "IssueHeaderID_FK". (note the "_FK" suffix)

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

Similar Threads

  1. key violations
    By Jen0dorf in forum Access
    Replies: 5
    Last Post: 08-10-2016, 12:20 PM
  2. key violations in append queries
    By louise in forum Queries
    Replies: 4
    Last Post: 06-07-2016, 08:55 AM
  3. Key Violations in an Append Query
    By petefc in forum Queries
    Replies: 1
    Last Post: 11-30-2012, 01:47 PM
  4. Key Violations
    By Daryl2106 in forum Access
    Replies: 5
    Last Post: 10-02-2012, 02:34 PM
  5. Database help, key violations
    By dhicks19 in forum Queries
    Replies: 6
    Last Post: 05-10-2012, 05:06 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