After Delete Macro with Long Text

Question

I have a table LABELS with columns CODE(short text), VALUE(short text) and DESCRIPTION(long text).

I also have a table LABELS_HISTORY with the same columns.

I created and After Delete macro on LABELS, which creates a new record in LABELS_HISTORY with the values of the deleted record from LABELS.

If I only use it for CODE and VALUE (which are short text), it works fine. But if i try to also copy the DESCRIPTION, it doesn't work (no error, just doesn't create a record in LABELS_HISTORY).

This is how the macro looks like: ( i can't embed images - newbie)

https://i.sstatic.net/igKlh.png

If you don't want to open the link:

Labels: After Delete: Create a Record In LABELS_HISTORY

SetField

         Name      Description

         Value   = [Old].[CODE]

it works fine with fields that are short text. Only problem is with Long Text.

Access 2016

Answer 1

Long text values are not available through [Old].. There is no work-around as far as I know.

This does generate an error. In your USysApplicationLog table, an error should be logged, that has the following description:

The '[Old].[DESCRIPTION]' value is not available because the field is of type memo, rich text, hyperlink, or OLE Object.

(memo is the old name for Long Text fields, thus these are not available).

You can also see this behaviour in MS SQL Server, where previous values of fields of the type Text and NText are also not available in triggers.

A piece of advice: always log the Current value in your change tables, not the Previous one. That way, you always have a full history of every record, and you don't run into trouble with these things.