Append data to memo field on cancel

[MarcusMaximus]

Hi i have an access database for tracking quotes and jobs, this includes a job memo section for any addition info on the job which is a memo field in the job table, tbljob.

One of the staff claims that some of the info is just disappearing out of the memo field which i find hard to believe. I recently did a compact and repair on the database and she noticed it missing then and she has it in her head that its missing because of this even though she doesn't know what compacting and repairing is. First of all is this possible, I myself don't think so.

Secondly, I'm trying trying to track changes made to the memo field. Currently I have separate form opening when they edit the memo field with a cancel option to that does a simple "docmd.close" when clicked clearing any changes made, which i think she may have clicked. It also has a save and exit button which saves changes and appends the user name and date of change using the following code.

    Me.JobLogMemo = Me.JobLogMemo & vbCrLf & UCase(" (" & DLookup("StaffFName & ' ' & StaffLName", "tblStaff", "StaffID=" & [Forms]![frmLogin]![cmbLoggedIn]) & " - Save & Exit - " & Date & ", " & Format$(Time, "short time") & ")")
    [Forms]![frm17Job]![txtJobLogMemo] = Me.JobLogMemo
    DoCmd.Close

This saves the job memo and appends the name of the last person to edit along with the date. I would like to have something similar where when cancel is pressed the the changes are discarded as normal but a note is appended saying the person cancelled. I don't want to remove the cancel option as they need the option to cancel and i can't use the above code changing the " - Save & Exit - " to " - Cancelled - " because it won't cancel any changes mad. Any suggestions.

I didn't design this form so if you have a better solution to the save and exit and cancel part where the user info who edited it is saved I'd be happy to hear about it as the current process seems wrong to me.

 

[MarcusMaximus]

What if i was to put the original memo into a text box that's invisible and when the user cancels copy the invisible text box into the visible one similarly to the save and exit option but appened the text "Cancelled on [Date] by [username]"

 

[MarcusMaximus]

That works creating a seperate text box to stop the original. JobLogMemoOrig being a separate text box on the form that invisible and not enabled s oit can't be edited.

'Cancel
Me.JobLogMemo = Me.JobLogMemoOrig & vbCrLf & UCase(" (" & DLookup("StaffFName & ' ' & StaffLName", "tblStaff", "StaffID=" & [Forms]![frmLogin]![cmbLoggedIn]) & " - Cancelled - " & Date & ", " & Format$(Time, "short time") & ")")
    [Forms]![frm17Job]![txtJobLogMemo] = Me.JobLogMemo 
    DoCmd.Close