none
SharePoint and Access - You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule. RRS feed

  • Question

  • We have a SharePoint list linked to Access which has been working fine for the last few months. We have a couple of users who use the Access DB to modify records in the SharePoint list. Suddenly we are now getting the following error when trying to modify any record or in Access: "You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule". We are using WSS 3.0 and Access 2007.

    You can still modify them in SharePoint with no issues. Any help will be really appreciate it.

     

     

    Tuesday, November 8, 2011 6:19 PM

Answers

  • This error occurs when Referential Integrity is setup on a table against another table.  This is typically used to prevent a user from adding data to a child list that does NOT exist in the parent list.

    For example, Products and Categories.  You want to assign a product to a category, but only if the category exists in the list.

    The opposite can occur as well, where if you try to delete a category, then the application needs to know what to do with all the would be orphans.

    So, I can definatively say what your problem is, but this is the basis.  Thus, do a little research in the setup of the relationships and see what you can learn.


    Steve Clark, MCTS | Twin-Soft.com
    Thursday, November 10, 2011 4:23 PM

All replies

  • This error occurs when Referential Integrity is setup on a table against another table.  This is typically used to prevent a user from adding data to a child list that does NOT exist in the parent list.

    For example, Products and Categories.  You want to assign a product to a category, but only if the category exists in the list.

    The opposite can occur as well, where if you try to delete a category, then the application needs to know what to do with all the would be orphans.

    So, I can definatively say what your problem is, but this is the basis.  Thus, do a little research in the setup of the relationships and see what you can learn.


    Steve Clark, MCTS | Twin-Soft.com
    Thursday, November 10, 2011 4:23 PM
  • JesusPM,

    It looks you have made changes in the list after initial connection to Access; if this is true than you will have to reconnect to the SharePoint list from within Access, hitting the refresh button will not be enough.  If you're trying to run an Append Query to a SharePoint list from within Access,  you have to  re-connect to the list after making changes to any choice fields in your SharePoint list.  Otherwise, your query may not work because it could violate data integrity rules.
    Other possible  reason for this issue could be datatype mismatch and thus a data type problem. Check and see what Access has assigned as a data type for each field imported from Excel text, date, number, etc.
     
    Refer following article for more detail on suggested solution:
     
    http://unitedsupplyco.sharepointsite.net/publicfacing/bensblog/Lists/Posts/Post.aspx?ID=40


    Leonid Lyublinski Wizard of MOSS
    • Marked as answer by star.warsModerator Monday, November 21, 2011 4:00 AM
    • Unmarked as answer by JesusPM Tuesday, January 17, 2012 4:41 PM
    Wednesday, November 16, 2011 6:47 PM
  • This just happened to me. It turns out that one of the other fields in the linked table had a value that was not included in the 'Choice' options on the Sharepoint page. For example, the Choice options were 'AM, EMEA, APAC' and the field value was 'AMERICAS.' Changing this to one of the available options made the record updatable as expected.
    • Proposed as answer by navyjax2 Monday, November 30, 2015 5:02 PM
    Tuesday, June 19, 2012 2:49 PM
  • I also had this type of issue. I could not find any data that I was loading that broke the integrety rules.

    What I found in the end was that existing data was breaking the rules and updating another field in the same record caused this error.

    Apparently SharePoint allows you to change the integrety rules even if your existing data breaks the rules. Most of the examples were SharePoint 'Choice' fields. However, I also had some 'People & groups' fields that link to the SharePoint user table. My IT department deletes users from SharePoint when they leave the company and this leaves records referring to a person that no longer exists in SharePoint.

    I hope this helps others with this problem

    Rory


    Regards Rory

    Monday, July 23, 2012 3:36 PM
  • This was the solution to this error for me as well. I changed the list of choices on a Choice field at some point and had two records with invalid choices entered. After I deleted the invalid selection in those two records, my update query ran successfully.
    Friday, May 31, 2013 4:02 PM
  • This just happened to me. It turns out that one of the other fields in the linked table had a value that was not included in the 'Choice' options on the Sharepoint page. For example, the Choice options were 'AM, EMEA, APAC' and the field value was 'AMERICAS.' Changing this to one of the available options made the record updatable as expected.

    This happened to me to and your answer helped me solve this issue, thanks!

    Joana Villas-Boas

    Wednesday, February 26, 2014 12:41 PM
  • I had this same message. However, there were no linked tables and the options selected in the item I was trying to modify were all present in the options column values. The only way I found to "fix" this was to change all of the option columns to allow "Fill-in" choices. Once I did this, I could modify the items in Access successfully and run update queries on them. Even after enabling the fill-in choices option, I didn't see values that were unique from the existing pick lists. The columns weren't marked as requiring a value, a unique value, or anything like that either. The only thing I can think of is this was a large list (15,000 items) and had a single indexed column (it wasn't a choice field, just text).
    • Edited by SJNBham Thursday, June 19, 2014 3:53 PM
    Thursday, June 19, 2014 3:51 PM
  • I was actually missing the option in a choice field that existed on a separate list.  My query was to copy a field into that 2nd list.  When I added the option into the choice field on the 2nd list, that was in the 1st list's choice field, it worked fine after deleting and relinking the table in Access.
    Monday, November 30, 2015 5:05 PM