Access AutoSave saves only edited field in table (1 Viewer)

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.

Try putting the code to your submit button in the beforeupdate event for the form and at the end of the code add cancel=true

CJ, Thank you so much for your prompt reply. That worked! I am not sure In understand why, but I can now get rid of the Submit button altogether.

Any book/site where I can understand which event fires when on an Access form?

Thank you so much for your help.

If you could click the Thanks button it will be much appreciated

Can't really recommend any books or sites - for me I just trying putting my question into the search box and investigate what it brings up.

With regards events you can try this link http://office.microsoft.com/en-gb/access-help/order-of-events-for-database-objects-HP005186761.aspx

which I found by searching on 'access form events order'.

Things get more complicated when using subforms (they open before the main form so any events which refer to controls on the main form will fail, but there are workarounds)

Happy coding!

The FORM's BeforeUpdate event is the LAST event to fire before a record is saved. Use this event to check for null or ZLS valurs and to do any multi-field validations such as dateA < DateB. You would then cancel the event with Cancel = True and set focus to the field in error.
There is absolutely NO reason to run an insert query (which is what it sounds like you are doing) to insert the current record. I can't tell from the query what the schema looks like and what fields are not getting filled. Post the schema and tells us which table and which fields are not getting filled. I'm guessing that you haven't selected the correct columns and that you have columns duplicated in several tables.