Track field changes in Access

[adubberke]

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!

[ranman256]

The child table tComments, will need a ParentKey (long integer). This will mate to the parent table key (you didn't say the table name) tHistory.

in the RELATIONSHIPS (on menu bar, DATABASE TOOLS, RELATIONSHIPS)
drag the tHistory.ID field to the tComments.PArentKey
enforce referencial integrity
cascade updates
(but NOT deletes)
save

now in the tHistory form, drag a sub-form onto the form. Set the dataobject to table.tComments
now all comments typed in will relate to the parent record.
(read up on this)

BUT to import, you MUST have the tHistory key to add to the tComment table.

[adubberke]

Hi Ranman,<br><br>Thanks for the response. &nbsp;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. &nbsp;One of the fields on this table is "Comment" because I want to be able to import an initial comment. &nbsp;I've created a 2nd table (Comments) where I would like to store the comment history. &nbsp;Will this current setup work with what you have suggested? &nbsp;Additionally, is it possible to capture the date/time of the comment and the windows ID of the individual who added a new comment? &nbsp;Again, thank you!