Error when adding values to an Access ADP form bound to a SQL stored procedure with a join

Question

I have an Access 2003 ADP project that connects to SQL Server 2008 Express Edition.

When I try adding values to a form that has as it's RecordSource a SQL Strored Procedure that uses a JOIN, I get the following error:

You can't update the record because another user or application deleted it or changed the value of its primary key.

The code for the Stored Procedure is:

SELECT F.Description, T.Quantity, T.Points
FROM Test T
RIGHT OUTER JOIN tblCriteriaCategory1 F 
ON T.FunctionalityID = F.tblCriteriaCategory1ID

(The values I'm trying to add are those for Quantity and Points to table Test)

I have also created the appropriate ForeignKey relationship on the Test and tblCriteriaCategory1 tables.

Thank you for any assistace with the above

BIBD BIBD · Accepted Answer · 2012-11-14T16:02:16

You probably need to make FunctionalityID the primary key for Test.

This knowledge base article show almost the exact same scenario as you are experiencing