Comments table

Question

I remember seeing this before and now I can't find it on the site.  I need to create a comments table.  I have a comments field in a form and when I update it I want it to write the comments to another table, but each time I type in the comment field I want it to time stamp infant of my typing.  And how do I link the comments table to the main table, with an ID number I presume or something, I have a ticket number that is entered on the form that is unique.

Answer 1

The simplest way to do this is to have a Comments table in a one to many relationship with the main table. In your case you'd use the ticket number as the primary key (or a uniquely indexed field, in any case) of the main table, and as a non-unique foreign key in the Comments table. One design that should work for the Comments table would be:

CommentID (autonumber, primary key)

TicketNumber (matching the size and datatype of your main table ticket number)

CommentDate (Date/Time, default value =Now() to automatically timestamp new records)

Comment (Memo, or Text if comments never exceed 255 bytes)

(possibly other fields such as the ID of the person entering the comment)

The user interface would be a Form based on your main table, with a Subform based on the comments table; use the TicketNumber as the master/child link field of the subform. This will let you enter new comments and see old ones, without having to use any code or to copy comments from one control to another.