How to populate an append only long text data type field

Question

I use a form to update/insert a database in Access 2016. I found Access has an option to record version of a field value change if data type of this field is long text and append only property is set to yes. I decided to concat all the values of a single record and save to this long text (history) field.

Now I have added an invisible textbox control to that form. The value of this field i.e. "history" will be populated when an event occurs (on unload from form current record) and that invisible textbox control value is set running a function.

So now I need to know which event should I address to populate history field?

I tried several events but all say conflict!

Answer 1

This seems like a last changed date kind of problem. I have has success with using the form's before update event for this. E.g.:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    With Me
        .LastChangedDate = Now()
    End With
End Sub

If you are having trouble accessing the history from the control you could set a vba variable to it using the Current event of the form. Be careful, the Current Event fires for many reasons and the code has to defend itself from running except when you want. I frequently test many conditions and switch settings so the Current event does not crash or worse. Trial and error needed. For example, you could record the key of the record whose history you have saved for later and test to see if it has changed before saving it again. Also, I make sure that initialization of the OPEN and LOAD events are complete along with any other initialization necessary before I allow the Current event to do anything significant.

Good luck.