Field value change tracking

[adubberkegre]

I've created a database in Access 2013 to track in transit item statuses. It is necessary to be able to input comments when communications about the items occur. Additionally, it is necessary to be able to see a diary of all the comments so users can easily view the history of the communication about the item. Previously, on my input form, I was displaying a "History" field that was utilizing the ColumnHistory function. It works fine when users create records one at a time; however, it appears to fail when records with comments are added via an import process. The initial comments that are included with the import are not included in the comment history.

In doing research, I've found that the solution might be to create a secondary table that will house the comments for each record and then display a subform datasheet on my current form. I am, however, at a loss on how to do this. I know how to create the table, but how do I get the "Comments" table to create a new comment record when I add a new item and how do I get the table to update when I add a new comment to an existing record?

Any help and guidance will be greatly appreciated!

[jonh]

Create new table. e.g. tblLog


Add your fields
ID (autonumber)
txt (text) - the user's comment
linkID (long int) - links the comment record back to your main data table
timestamp (date time) - time of comment
user (text) - who added the comment




Add some code to a form button



CurrentDb.Execute "insert into tblLog ([txt],[linkID],[timestamp],[user]) values (" & _
"'" & InputBox("Add note") & "'," & _
Me!id & "," & _
"#" & Now & "#," & _
"'" & Environ("username") & "'" & _
")"


When the user presses the button an input box is displayed to type the message into.


Use a query to link the two tables together via ID/LinkID and add to your subform.

[adubberkegre]

Hi Jonh,

Thanks for the response. So, I have a main table (Issues) where I capture all the information pertaining to the item (i.e., id number, location, person assigned to it, etc), and this the table I import to. One of the fields on this table is "Comment" because I want to be able to import an initial comment. I've created a 2nd table (Comments) where I would like to store the comment history. Additionally, I'd like the information to feed to the "Comments" table after an update to the "Comments" field on my form rather than through the push of a button. Will this current setup work with what you have suggested? Again, thank you so much!

[SamT]

I would record even the original comment in the CommentLogTable.

Set a Form level Boolean Variable when the comment is changed and if set, then On_Update, include updating the CommentLogTable.

This would allow the Form displaying the original, the last, or all comments, depending on their timestamp.

[adubberkegre]

Hi SamT. How would those things be accomplished? I'm sorry if that's a silly question. I'm still quite a novice when it comes to Access.

[SamT]

I'm still quite a novice when it comes to Access.Me too. But it just makes sense. Why store some comments in one place and the rest in another?

[adubberkegre]

No, I agree. Ideally, all comments would be stored in the comments table, but I need to be able to import comments with my import process, and that import process appends to the "Issues" table, not the "Comments" table. Unless you can append to multiple tables when you import??? I didn't see that as something that can be done, but like I said, I'm still a novice, so it might just be a process I'm not familiar with.

[jonh]

There is no process to be familiar with really just basic database structure.
All data of a particular kind is stored together. All comments go in one table.

You cant expect to change your table structure without changing your import steps.
If your data is split you need to split your import.

Import to a temp table and use two (or more) queries to put the data into the right tables from there.

As to using a field to update comments rather than my code. Of course, that's what forms are for.