Update/Append corrupting Memo field after 512 characters

[completelybobbins]

Update/Append corrupting Memo field after 512 characters (Resolved)

Hi Folks

Can't find anything on searching about this, but apologies if I've just missed something...

Example db attached (open form 'frmTest')

If I load the contents of a memo field into a form's textbox, add any characters which make the string longer than 512 (ASCII?) characters (with carriage returns counting as 2), and then try and update the record with the contents of the textbox, all characters after the 512th are scrambled.

No formatting on either the textbox, or the table field.

This occurs with update and append. If I enter the extra characters directly into the table the problem doesn't occur. No '," or anything that might cause sql problems.

Comments appreciated, even if it's just a could not reproduce! I'm convinced this must be some daft setting I've missed, otherwise the web would be covered in posts about it.

Cheers

Rob

 

[boblarson]

Change your update code to this:

Private Sub cmdUpdate_Click()
    SQL = "UPDATE tblTest SET tblTest.[testMemo] = " & Chr(34) & [Forms]![frmTest]![testMemo] & Chr(34) & _
" WHERE (((tblTEST.testID)= " & [Forms]![frmTest]![testID] & "))"
    DoCmd.RunSQL SQL
    
End Sub

So it is not having to try to evaluate the values but instead you are passing the explicit values from the form references. It doesn't have a problem if you do that. (also, it doesn't have a problem if the form is bound).

 

[boblarson]

Oh, and I would change to using CurrentDb.Execute instead of DoCmd.RunSQL:

    CurrentDb.Execute SQL, dbFailOnError

 

[completelybobbins]

Huge thanks Bob - wouldn't have even thought of trying that.