I am using Access 2010 with linked tables from SQL Server 2008. I have a form that takes a QCNote from the end user. The control source query of the form looks like this:
SELECT B.QCDecisionPointID, B.Description AS [Decision Point], C.QCResultDecisionPointID, C.QCNote, C.QCResult, C.NoteText, C.QCResultAttributeID, A.QCAttributeID, [Forms]![QCDocAttributes]![QCResultAttID] AS QCResultAttID, [Forms]![QCDocAttributes]![AssignmentID] AS QCAssignmentID
FROM (QC_QCAttributeDecisionPointAsc AS A INNER JOIN QC_QCDecisionPoint AS B ON A.QCDecisionPointID = B.QCDecisionPointID) LEFT JOIN (SELECT * FROM QC_QCResultDecisionPoint WHERE QCResultAttributeID=[Forms]![QCDocAttributes]![QCResultAttID]) AS C ON B.QCDecisionPointID = C.QCDecisionPointID
WHERE (((A.QCAttributeID)=[Forms]![QCDocAttributes]![AttributeID]));
The user adds a note and then navigates to the next record. The table QCResultDecisionPoint is updated with the new QCNote added. But the other fields in the table :QCAssignmentID, QCDecisionPointID, QCResultAttributeID remain null. Since AutoSave saves only the one field in the table, I added a Submit button that correctly saves all the information. But I end up with two records now, a partial one saved by Access, and a complete one saved by the Submit button event. When a user has added a note, then navigates to the next record and he/she decides to go back to the previously added note, the note appears as #Deleted. When the form is closed, I have currently added a delete statement to remove the partial record saved by Access, so if the user closes the form and goes back to see his/her note, then the note is displayed correctly. The problem is when you go back to a freshly added note without closing the form, it appears as #Deleted to the end user. Any light you can shed on this issue? How to prevent #Deleted? How to get Access to save all the fields? Thank you in advance.
SELECT B.QCDecisionPointID, B.Description AS [Decision Point], C.QCResultDecisionPointID, C.QCNote, C.QCResult, C.NoteText, C.QCResultAttributeID, A.QCAttributeID, [Forms]![QCDocAttributes]![QCResultAttID] AS QCResultAttID, [Forms]![QCDocAttributes]![AssignmentID] AS QCAssignmentID
FROM (QC_QCAttributeDecisionPointAsc AS A INNER JOIN QC_QCDecisionPoint AS B ON A.QCDecisionPointID = B.QCDecisionPointID) LEFT JOIN (SELECT * FROM QC_QCResultDecisionPoint WHERE QCResultAttributeID=[Forms]![QCDocAttributes]![QCResultAttID]) AS C ON B.QCDecisionPointID = C.QCDecisionPointID
WHERE (((A.QCAttributeID)=[Forms]![QCDocAttributes]![AttributeID]));
The user adds a note and then navigates to the next record. The table QCResultDecisionPoint is updated with the new QCNote added. But the other fields in the table :QCAssignmentID, QCDecisionPointID, QCResultAttributeID remain null. Since AutoSave saves only the one field in the table, I added a Submit button that correctly saves all the information. But I end up with two records now, a partial one saved by Access, and a complete one saved by the Submit button event. When a user has added a note, then navigates to the next record and he/she decides to go back to the previously added note, the note appears as #Deleted. When the form is closed, I have currently added a delete statement to remove the partial record saved by Access, so if the user closes the form and goes back to see his/her note, then the note is displayed correctly. The problem is when you go back to a freshly added note without closing the form, it appears as #Deleted to the end user. Any light you can shed on this issue? How to prevent #Deleted? How to get Access to save all the fields? Thank you in advance.