Can I set a memo field so that so that it can retain its old & new values?

[TonyRandom]

I have a comments field on a form. I want the field value to be copied to a variable, so that when the user updates or changes the value of the field, the field value will be something like:

Dim OldValue as Variant
Dim NewValue as Variant
OldValue & vbCrLf & vbCrLf & NewValue & vbCrLf & Now

so that a constant log is kept for changes to that field?

Would something like the above work? And if so, which event would I put it in?

Any help appreciated.

[Rabbit]

I wouldn't recommend that method to keep a history of comments. You should have a separate comment table that is linked to that table.

[TonyRandom]

Thanks Rabbit, I see what you are saying, I should set up a side table on a one-to-many relationship. I will be displaying the form in a datasheet view, and the comments are just for short notes for the life of an order.

I will keep pecking at it, but will probably go with a subdatasheet view using the linked table. :)

[TonyRandom]

I have cracked it! I am a little bit worried that I am going to have problems though. Would I get any advantage using a linked table?

Here is what I used

Private Sub memComments_AfterUpdate()
Dim MyOldValue As Variant
Dim MyNewValue As Variant
Dim MyNewFieldValue As Variant
 
MyOldValue = Me.ActiveControl.OldValue
MyNewValue = Me.ActiveControl.Value
MyNewFieldValue = MyNewValue & vbCrLf & Format(Now, "dd/mm/yy hh:nn:ss") & vbCrLf & vbCrLf & MyOldValue
Me.ActiveControl.Value = MyNewFieldValue
End Sub
 

[Rabbit]

There is a problem with what you're trying to do in the code. If they typed in this is a comment. and if they don't overwrite it and instead append to it. this is a comment. this is a comment too.. Then when the code runs, you get this is a comment. this is a comment. this is a comment too.. Plus there would be no way of deleting the duplication without going into the table directly.

[TheSmileyCoder]

What version of Access are you using? I belive that access 2007 has an append only memo field built into it, though I have not used it.

The advantage of a seperately linked table would be that you could better keep track of QA data, such as who created it and when. Now you example does add the date, but it would not be in a searchable format, whereas that would be possible if the creation date had its own field in a seperate table.

Another option is to store it in a seperate field, but display it in a combined memo field. Depends on your requirements.

One thing to note though, is that I would recommend against tying it to the afterupdate, since I could imagine a situation in which a user starts to type something, then regrets and goes to cancel, but as soon as the focus leaves the field, the memo gets update, and so also does your memofield. You could use a similar approach but tie the update to the forms update event instead. Just a thought.

[TonyRandom]

Hello TheSmileyCoder,
I am using Office 2010 Professional, but have only recently upgraded to it. Previously used Access 2000, and I am still getting used to where things are.

I have noted your comments regarding the appropriate event, and will give it a try. Not quite sure how it will work though. I guess I just change the Me.ActiveControl. references to me.ActualControlName and put it in the Forms.Afterupdate event.

Thanks for all the help.

Tony