Memo field dropping edits after tab-out

[GrandMasterTuck]

I have a field on a form that's hooked to a PersonNotes column in my table, which is set to MEMO instead of TEXT (so there's no character limit). The field has an AFTER UPDATE command that writes the edit to an Audit Trail (records the username, date, time, field name and field contents) so I have an accounting that the action was taken and by whom.

Here's the problem: I pull up a record, edit the PersonNotes field, tab out, and my edit vanishes and the field returns to what it said before I made the edit. SO I checked the Audit Trail, and the Audit Trail record shows the edit that I made, which means it was recorded properly there. Then I checked the underlying table (which is hooked to the form on which this troublesome PersonNotes field sits) and the edit I made is NOT in the table.

What could cause this? The "AddAudit" function is a simple SQL INSERT INTO statement that's assembled using VBA, and never touches the table that form's hooked to. The function simply takes the value of the PersonNotes field and concatenates it into a line of text with the UserName, Date, Time, and Field Name, and then runs that SQL INSERT INTO statement to write this assembled string into the Audit Table, so it doesn't have anything to do with that particular field.

Is there some bug to Access forms that causes this kind of thing? Or something I should be checking to make sure it's not set incorrectly? The table's PersonNotes column is set to MEMO, so I know it's not dropping characters after 255... I'm stumped. Any ideas?

 

[GrandMasterTuck]

No, I use TempVars to pass strings along to the INSTERT INTO statement assembler. This part is in the AfterUpdate of the PersonNotes field:

SetTempVar "TheAudit"
Expression: [IDBox] & " Set/Updated Person Notes: " & [Text45] & " on " & Date() & " at " & Time()
RunCode AddAudit()

Then the VBA behind the AddAudit() is as follows:

Function AddAudit()
Dim SQL As String
    SQL = "INSERT INTO tblAudit (TheAudit) " & _
             "VALUES ('" & [TempVars]![TheAudit] & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
End Function

And that's it. No other code on the form, no other code on the field, no other code in the table.

 

[JHB]

I'm a little confused reading your post, could you post your database with some sample data?

 

[Gasman]

I have a field on a form that's hooked to a PersonNotes column in my table, which is set to MEMO instead of TEXT (so there's no character limit). The field has an AFTER UPDATE command that writes the edit to an Audit Trail (records the username, date, time, field name and field contents) so I have an accounting that the action was taken and by whom.

Here's the problem: I pull up a record, edit the PersonNotes field, tab out, and my edit vanishes and the field returns to what it said before I made the edit. SO I checked the Audit Trail, and the Audit Trail record shows the edit that I made, which means it was recorded properly there. Then I checked the underlying table (which is hooked to the form on which this troublesome PersonNotes field sits) and the edit I made is NOT in the table.

What could cause this? The "AddAudit" function is a simple SQL INSERT INTO statement that's assembled using VBA, and never touches the table that form's hooked to. The function simply takes the value of the PersonNotes field and concatenates it into a line of text with the UserName, Date, Time, and Field Name, and then runs that SQL INSERT INTO statement to write this assembled string into the Audit Table, so it doesn't have anything to do with that particular field.

Is there some bug to Access forms that causes this kind of thing? Or something I should be checking to make sure it's not set incorrectly? The table's PersonNotes column is set to MEMO, so I know it's not dropping characters after 255... I'm stumped. Any ideas?

This believe this link explains why

http://allenbrowne.com/ser-63.html