Show date last updated on form

[Guest]

I have 6 comment fields on my form that is updated by 6 department heads.
What I want to do is add a date/time stamp field for each of these comment
fields so anytime the field is updated it will show the last date/time the
field was updated. How would I set this up?

 

[Arvin Meyer [MVP]]

To be properly normalized, you should have a subform to a separate table
with the ID of the department head, a comments field and a date field (which
can be hidden or locked so it can't be edited. Now, set the DefaultValue
property of the date field to:

= Now()

As the editor adds his comment, the timestamp fills automatically. I'm
going to assume that there will never be editing of an existing comment. You
can enforce that by only showing new records.

If you must edit an existing comment, add a bit of code to the BeforeUpdate
event:

Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtLastUpdateControl = Now
End Sub

 

[Guest]

Not sure I need the separate table with the ID's. Each department head has a
designated comment field and they know only to fill in their respective
field.
If I set the default value to "=Now()" that will show the current date
whenever a new record is added. There are other fields on my form that will
be filled in but sometimes the comment fields may not be used for that
particular record. I don't want the default value to always populate the
current date on a new record. I only want this date to be populated when
comments are added or when they are modified.

I probably should have explained this a little better in my orignal post.
Sorry about that. I do have an unbound field next to each comment field where
I want the timestamp to be populated. That field is locked so nobody can
change it.

Your thoughts?

 

[Guest]

Actually I have it working. I did end up creating another table to store the
updates and used the BeforeUpdate event to trigger the date.

Thanks for your help Arvin!

 

[Douglas J. Steele]

I'd have to agree with Arvin that your design doesn't sound properly
normalized. If you've got 6 separate fields, one for each department head,
you've got what's referred to as a repeating group. What are you going to do
when the company reorganizes, and now have seven departments?

Even if the department doesn't reorganize, writing queries such as "which
entries have comments from only four department heads" or "tell me the order
in which the department heads commented on issue xyz" are quite difficult
with your design, but fairly easy with a properly normalized design.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Guest]

I'm not sure I totally understand your approach. Are you saying create a
separate table with my comments fields and then link them to my main table?
What would be the difference if they are in my main table or not? Not sure I
understand what the difference would be.

 

[Douglas J. Steele]

The normalized approach would have two tables. One's essentially the table
you have now, except for the 6 memo fields. The other table would link to
the first table, and would have one memo field, so that you'd have a row for
each comment. Whatever the PK is for the first table, that plus a CommentNb
field would be the PK candidate for the second table.

Typically, you'd maintain this using a form/subform: the parent form is
where you'd maintain the information in the first table, and the subform
would be where you'd maintain the information in the second table.

In that way, when you get a seventh department, you now simply need to have
a seventh row in the second table, as opposed to adding another memo field
to your existing table and then having to change all your forms and reports.

For more on normalization, start with what Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Guest]

Thanks Doug. I will research things on Jeff's site.