keep running information in a field

[laurat]

I am designing a database that has several different tables, one table is a comments table. Each other table has a button that brings a pop up form to enter the comments. The users of the database would like all comments to remain in the table, meaning all new comments for one record get added the comments that were originally placed. For example, they go in one form and add the appropriate info for a record and click the comments button and add the appropriate comments. Than another user goes to the same form or another form and enters info about the same record and clicks the comments button to enter their comment. The users would like both comments to be stored in the table for that record. I am new at all of this and am not sure how to go about this. Any help would be appreciated. Thank you.

 

[Stephenn]

I’m new at this also, but what I have works for me. I used two fields on the table. One called “Response” (where you enter comments) and one called “History” (where a running history of comments is stored and displayed). The following code is on the “After Update” event on the “Response” field. The following code will also time and date stamp the comments and delete the contents of the “Response” field (because the response has been fed to “History”).

Response = Response.Text
History = History & Now() & ": " & Response.Text & " " & Chr(13) & Chr(10)
Response = " "

I then locked the “History” field so that users couldn’t edit past comments.

I’m sure there’s better code out there, but maybe this will give you a start.

Also, I was given the following link for help with an audit trail. It wasn’t what I needed at the time but have been using on other DBs since then. I recommend taking a look.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q197592

HTH
Stephen


[This message has been edited by Stephenn (edited 03-22-2002).]

 

[laurat]

Thank you, I tried the code given to me and it worked how I needed it to!! My only question is Did you make your field a memo field, if so, what happens when it exceeds the 256 character capacity??

 

[Stephenn]

I believe they were both memo, but I need to double check. I'll look and get back to you.
Stephen

 

[Stephenn]

Yes, both fields are memos. In answer to your second question, "Text" data type is the type that is restriced to 255 characters. "Memo" has the following definition in Access help:

"Up to 65,535 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)"

Glad it's working for you.
Stephen

PS. I recently changed the script so that new text added would be added to the "top" or Begining of "History". This way you read the latest entry first and the entries become "older" as you read along with the first entry being last. I changed:

History = History & Now() & ": " & Response.Text & " " & Chr(13) & Chr(10)

to

History = Now() & ": " & Response.Text & " " & Chr(13) & Chr(10)& History

Stephen


[This message has been edited by Stephenn (edited 04-03-2002).]

 

[laurat]

Thank you Stephen for the help. It does make sense to have the most recent at the beginning. I will change mine also. Thanks.