trying to track deletions in a text box

[kwren777]

I am trying to make a comment box (text box/memo field) tag a user name and
date for any comments posted on a form in an Access 2002 db (DAO). I have set
up in VBA to add the user ID and date.

My trouble begins when I delete something in the textbox (like another
person's comment), it will "tag" the textbox with my user ID and date (even
though that comment is not mine). Is there a way to prevent edits or
deletions to past comments? Or to compare the OldValue to the NewValue to be
able to note what was deleted with the user ID and date for who deleted it?
Or even, just to note that something in that text box was deleted?

Currently this is my VBA code:
Private Sub txtComments_AfterUpdate()
Dim str As String
Dim str2 As String

If Me.txtComments.OldValue <> Me.txtComments.Value Then
str = Me.txtComments & " - " & username() & " " & Now() & vbCrLf
Me.txtComments = str
ElseIf Me.txtComments.OldValue = Me.txtComments.Value Then
MsgBox ("Nothing has Changed. Do you want to continue?")
' ElseIf Me.txtComments.OldValue > Me.txtComments.Value Then
' str2 = Me.txtComments & vbCrLf & "Comment Deleted " & UserName() &
" " & Now()
' Me.txtComments = str2
End If
End Sub

The "commented" section is what I would like for it to do, but I know that <
& > with string comparisons can be unpredictable. Ultimately, I would check
for <>, then check if there were only comments added or if something was
deleted.

IF oldvalue <> newvalue then
IF someone.deletedcomment then
comments = commments & vbcrlf & "deleted comment" & username
else
comments = comments & vbcrlf & username
endif
endif

TIA,
kwren

 

[Allen Browne]

A really simple solution to allow new comments but not allow old comments to
be destroyed is to create a related table to handle the many comments
entered over time.

This table has fields like this:
NoteID AutoNumber primary key
FKID Number which ID from the main table this comment is
for
UserID who entered this comment
NoteDate Date/Time when the note was entered
TheNote Memo contents of this note.

The interface would be a subform in Continous View, so you can see multiple
notes over time. For this form, set properties:
Allow Edits No
Allow Additions Yes
Allow Deletions No
Now users can add new comments, but not modify or delete old ones.