Memo field being corrupted

[silverback]

I have a table which contains a memo field and a form which contains an unbound text box in which the memo field's text is displayed. The user selection an option using a combo box on the form and an AfterUpdate event obtains the contents of the memo field from the correct record using a DLookup which has a criterion based on an Autonum contained in the option selected.

If the user alters the text, it needs to be written back and this is being done using an AfterUpdate event which runs an Update query, building the string and then using DoCmd.RunSQL

When writing back short amounts of text, all seems to be working OK. With long amounts of text the text is truncated and the last three characters of the written back text contain random characters.

Has a limit been reached? Is that why the data is being truncated and why are there random characters at the end?

Thanks
Silverback

[HansV]

There are several situations in which the contents of a memo field are truncated to a string with a maximum length of 255 characters, for example if a format is applied to the memo field, or if records are grouped by the memo field. But I haven't heard about the contents being corrupted.

Have you tried to compact and repair the database? If so, does it make a difference?

Could you post the code you're using?

[silverback]

Hans
I've stripped down the DB and attached it. None of the underlying queries are present, but you can select options and display the text, alter it and have it written back.
Note that the SalaryReviewData option text has been left in its corrupted state; all I did to test the code was add a period at the end of the text but when next the text was displayed it had been truncated and had the random characters at the end.
Thanks
Silverback
PS : Compact and repair had no effect

[HansV]

I'm confused - the form DataExtraction is bound to the ExtractionOption table but you appear to treat it as an unbound form: none of the controls is bound to a field in the table.

[silverback]

That is a mistake. It shouldn't be bound to the table as the only thing which alters the table is the 'Explanation of what the Chosen Option does", and this is done by the DoCmd.RunSQL Update code.
It's not an Access requirement that a form is bound to a table if it updates a field in the table, is it?
Silverback

[HansV]

You can update a table from an unbound form without problems. You should clear the Record Source property of the form.

The line

Code: Select all

    strUpdateSQL = "UPDATE ExtractionOption SET ExtractionOption.[DENotes] = CodeContextObject!DENotes "

in DENotes_AfterUpdate should be

Code: Select all

    strUpdateSQL = "UPDATE ExtractionOption SET ExtractionOption.[DENotes] = '" & CodeContextObject!DENotes & "' "

for CodeContextObject is known in VBA but not in SQL.

[silverback]

Once again, many thanks.
RecordSource was set up during a previous try at getting the form to work, and it was sloppiness on my part for not tidying it up when I changed my approach.
I'm afraid I got a bit keen on the CodeContextObject method after you told me about it in a recent posting. The text is now being written back successfully - thank you.
Silverback