Adding text to a memo field

[Guest]

I have a table with an MEMO field that records comments from users, I am
worried that a user may overwrite the text in the memo field if they need to
include any comments on the case. (Not all officers are Microsoft savvy and I
know if they tab onto the memo field, as it highlights all the text, they
could just possibly overwrite what may be recorded previously)

Would it therefore be possible to create a comments text field, so when text
is input, access will merge this into the MEMO field with any other comments
that may have been previously recorded.

If it is possible and simple to do, the procedure to set this up would me
most welcome.

 

[Rick B]

Typically, the way to do this would be to add a NEW comment each time. This
is a one-to-many relationship where the comments would be stored in a
separate table. Most people would handle the input by including a subform
in their main form where previous comments can be seen, and new comments can
be added.

If you want to see this, open the "Contact Management" sample database and
take a look at how it stores phone contacts.

Another way would be to create a field ("Additional Notes:") on your form.
When the field is changed, you would have code run that concatenates the
entry with the previous comment. You could even include the UserID that
made the entry and the Date/Time. You could also include a carriage return
if you want it on a new line.

 

[SusanV]

Sure - so long as your sure no one will need to actually edit this memo
field. Simply set the memo field Locked property to Yes and add an unbound
textbox to the form, lets call it txtComment. In txtComment's after update
event, use code similar to the following:

Dim strComment as String
strComment = Me.txtComment
me.YourMemo = Me.YourMemo & vbCrLf & strComment

The vbCrLf brings the new text to the next line - you might want to use 2 of
them to differentiate between the comments, and you can even datestamp the
comments if you like:

Dim strComment as String

strComment = Me.txtComment
me.YourMemo = Me.YourMemo & vbCrLf & vbCrLf & Format(Date, "mm-dd-yyyy") &
vbCrLf & strComment

 

[Rick B]

I tested this and found that to do option 2, you would need to add a new
field to your form. Then, in the "AfterUpdate" event on that field, put
code similar to the following...

Private Sub Text49_AfterUpdate()
Comments = Comments & " " & CurrentUser() & " " & Now() & Text49
EndSub

Of course you can name yours something more interesting than "Text49"


You might also make the "Comments" field not enabled so the user can't go in
and mess it up.

 

[Guest]

Thanks for the fast response, this sounds like what I need, including user,
date/time etc.

"Another way would be to create a field ("Additional Notes:") on your form.
When the field is changed, you would have code run that concatenates the
entry with the previous comment. You could even include the UserID that
made the entry and the Date/Time. You could also include a carriage return
if you want it on a new line."

Would it be possible to point me in the right direction when it comes to
coding / setting this up, as my access ability is quite limited.

Thanks again, this will be so neat!

 

[Guest]

Thank you "Rick B" & "Susan V" this is just what I needed.

One last thing, is it possible that when I tab away from the txtComment
field it makes the field blank, as the comment currently stays on all the
records.

Thanks again

 

[SusanV]

Hi Re-man,

Add a line to the end of the code:
Me.txtComments = ""