Modify D/T for Multiple Fields in the Record

[Murphy0417]

Lots of great stuff on this forum about automatically adding modified date/time to a record as it changes, but I wasn't able to find a thread on this particular question:

I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.

Is this possible?

[pbaldy]

You could run an update query with the appropriate criteria to modify multiple records. Frankly needing to do so implies a normalization problem.

[John_G]

Hi -

A word of caution using that approach - yes, you can update records when a box is checked - but you must include code to handle the case where the box is un-checked again, for example if it was checked by mistake.

If the sequence of approvals is important - i.e. the 5 people must approve the document in a specific order, you need to deal with that, too.

HTH

John

[Murphy0417]

You could run an update query with the appropriate criteria to modify multiple records. Frankly needing to do so implies a normalization problem.

Thank you very much for your feedback. I'm in the early building stages so I'm open to suggestions on how to revise the structure to solve the normalization problem, if you have any thoughts. Thanks again!

[Murphy0417]

Hi -

A word of caution using that approach - yes, you can update records when a box is checked - but you must include code to handle the case where the box is un-checked again, for example if it was checked by mistake.

If the sequence of approvals is important - i.e. the 5 people must approve the document in a specific order, you need to deal with that, too.

HTH

John

Excellent point about the un-checking for a mistake. I think I'm going to use manual entry for time stamps for that part of the report to give them flexibility to change the date/time if they don't get into the form until a day or two after the document was moved to the next stage.

What I'd like to still use this approach for is a set of note fields. Right now I have 10 fields (Note01, Note02, etc.). I'd like to be able to auto update an associated date/time field to then display on the form when the note was entered/modified. This would remove the mistaken entry, un-check issue since I'd want to document the time stamp of any changes to any note.

Any ideas?

[John_G]

Having 10 fields (note01, note02...) does indicate a normalization problem. You should have separate table for notes, with fields for workflow item number (i.e. the unique ID of the record containing the Note01, note02... fields), the time stamp when the note was entered, and the note text. You don't need a separate note number field, because the date/time automatically sequences them for you. If the notes may be edited after they are entered, you could have two fields, Date/time entered (which should not be changed), and date/time modified.

[pbaldy]

Numbered fields like Note01, Note02 is almost certainly a mistake. You might read up on normalization. One link among many:

Fundamentals of Relational Database Design -- r937.com

[Murphy0417]

Thank you both. That makes sense, having a separate table for the notes. The form could commit a new record to that table (a single point of entry instead of 10), and then display the previous notes for that record ID in order of date/time entry.

Very good advice and thank you for the link. I've been Excel heavy most of my career and trying to learn the correct way to do things in Access. This forum, and people like yourselves being willing to help, is huge. Thanks again!

[pbaldy]

Happy to help. The design philosophy for a relational database is definitely different than for a spreadsheet. Your Excel knowledge almost puts you at a disadvantage. You'll get it though!