Store Date/Time of Comments within Comment box

[Chertsey]

I am using Access 2003.
I have a comment box where users will be leaving numerous comments at
various points in time for a particular record. I would like to capture at
least the date/time that a comment is made, and then capture the date/time
the next comment is made for the same record. I am hoping to capture this
Date/time within the comment field itself. So essentially the comment box
would be accumulating these numerous comments and storing the date/time they
were made. Can this be done?
An additional wish would be to capture the user who made the comments, but
not necessary.
Thanks for any suggestions?

 

[fredg]

I am using Access 2003.
I have a comment box where users will be leaving numerous comments at
various points in time for a particular record. I would like to capture at
least the date/time that a comment is made, and then capture the date/time
the next comment is made for the same record. I am hoping to capture this
Date/time within the comment field itself. So essentially the comment box
would be accumulating these numerous comments and storing the date/time they
were made. Can this be done?
An additional wish would be to capture the user who made the comments, but
not necessary.
Thanks for any suggestions?

Code the Comment control's AfterUpdate event:
Me.[ControlName] = Me.[ControlName] & " " & Now()

which will add the date and time after your text.

 

[GeorgeAtkins]

How about using an AfterUpdate event? Something like this:

Private Sub txtComment_AfterUpdate()
txtComment = txtComment & " " & Date & " " & Time
End Sub

Assume the textbox for the comment is named txtComment. When the field is
exited (or the record is saved), the commentbox is repacked with all of the
stored comments. Then the current date and time are appended at the end of
the comment. After each edit, the latest date and time are appended to the
end, assuming new comments are added at the end. This isn't bullet-proof,
since any kind of edit in the textbox will elicit a new date/time stamp,
even correcting
spelling in an existing comment.

Since you are using Access 2003, if you have set up user-level security, you
can determine the user with the CurrentUser method. Otherwise, it always
returns the default "Admin" account name.

Otherwise, you can try using an API function call to obtain the name through
Windows, assuming the user logged in that way.

1. At the top of a module page (above all procedures), paste in the following:
Declare Function GetUserID Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

2. Now paste in the following function in the module:

Function ReturnUserName() As String
Dim lR As Long
Dim sDump As String, UserName As String

sDump = String(255, " ") 'Create a buffer
lR = GetUserID(sDump, 255) 'Fill buffer with ID
UserName = Left$(sDump, InStr(1, sDump, vbNullChar) - 1) 'Return just the id
ReturnUserName = UserName

End Function

Now you can reference the function in the original code I gave you,
something like this:
txtComment = txtComment & " " & Date & " " & Time & " by " & ReturnUserName

I got this API solution from the Xtreme VB Talk web site, by the way. Good
luck!

 

[KARL DEWEY]

What about putting the comment in another table and have a one-to-many
relationship?

 

[John W. Vinson]

I am using Access 2003.
I have a comment box where users will be leaving numerous comments at
various points in time for a particular record. I would like to capture at
least the date/time that a comment is made, and then capture the date/time
the next comment is made for the same record. I am hoping to capture this
Date/time within the comment field itself. So essentially the comment box
would be accumulating these numerous comments and storing the date/time they
were made. Can this be done?
An additional wish would be to capture the user who made the comments, but
not necessary.
Thanks for any suggestions?

Rather than using one big mishmosh of a memo field for multiple dates and
comments, you may want to consider having a one to many relationship to a
Comments table; this would have a foreign key field linking to your main
table, a memo field for the (individual) comment, a Date/Time field defaulted
to =Now() to capture the time that the comment is made, and a UserID field
defaulting to the user's ID. You'ld have a subform based on this table using
your table's primary key as the Master Link Field and the foreign key as the
Child.

 

[Yuta]

Thanks very much to all.
I've now got some ideas to test over the weekend.
John W's sounds like the right way to go. It's just a little more
complicated than I wanted as I have several tables in my DB that have a Memo
field. But this idea might work. Busy weekend ahead...thanks again.

 

[Bob Quintal]

Thanks very much to all.
I've now got some ideas to test over the weekend.
John W's sounds like the right way to go. It's just a little more
complicated than I wanted as I have several tables in my DB that
have a Memo field. But this idea might work. Busy weekend
ahead...thanks again.

Do not add multiple comment tables to the database, Expand on John's
method and add a field to the comments table to store the table name
in addition to the foreign key field linking to your main table

Q