I am trying to update a field in a SharePoint list from values stored in a table which resides on our SQL server(2008) which uses Access(2007) to manage the connection and queries. I had to change the table/field names for security reasons. The error that I’m getting is “You cannot record your changes because a value you entered violates the settings defined for field ‘field2’ (for example, a value is less than the minimum or greater than the maximum). Correct the error and try again.”

SPList:
Name Type Req.
Field1 Single line of text yes
Field2 Lookup No
Field3 Number No

Table1:
Name Type
Field1 AutoNumber
Field2 Text

Table2:
Name Type
Field1 Number
Field2 Number
Field3 Number

Query:
UPDATE [SPList]
INNER JOIN (Table1
INNER JOIN Table2
ON Table1.Field1 = Table2.Field2)
ON [SPList].[Field1] = Table1.Field2
SET [SPList].[Field3] = Table2.Field1
WHERE ((([SPList].[Field1])="11111")
AND ((Table2.Field3)=5 Or (Table2.Field3)=10 Or (Table2.Field3)=15));

This post has been edited by jaw_mechwarrior: Feb 23 2012, 03:00 PM

First, welcome to UA!!! (IMG:style_emoticons/default/welcome2UA.gif)



Access can return an error on a field not being used, especially a datatype error.

You didn't change a datatype when you modified the table?

Thanks for the welcome (IMG:style_emoticons/default/smile.gif)

I have not made any changes to the data types on any of the tables, unless the query is somehow making the change which is not intended.

Resolved the issue, it was a permissions issue with the SP site not allowing me to have access to one of the lists that was connected to the list called in my query.