How to automatically update date field on change and archive previous data

[mara9642]

Good evening,



How do I set a form so that when the values of 1 of 3 text boxes is changed the previous value is archived in another table and the date of the latest change is displayed automatically in another text box?

For example I have 5 text boxes: date entered, date updated, description, status, and comments.

If the user updates [status] I want to save the previous value of [status] with the value of either [date entered] or [date updated] (depending on whether it's an update to an existing entry or a new entry) in another table.

After the user leaves [status], I want to update [date updated] with the current date and time.

I would like this functionality to work the same way for [description], [status], or [comments].

Thank you for the help,
Theresa

[June7]

Perhaps each status update should be a new record in a related table.

[Micron]

What, 3 separate tables? 1 for status, 1 for description, 1 for comments?
First, if the comments field is long text/memo, AFAIC, it should be its own table regardless. Comments are sometimes best appended and not allowed to be edited.
Second, I think you'll have to code for logic like
- if either of those 3 fields are altered, copy the existing record to tblHistory, then update the "main" table(s). I have no idea what you'd want to do when it's a new record - probably just append. The code logic would have to deal with both cases. If the fields are bound, maybe use the OldValue property to discern what the change is. Probably could use the Form.NewRecord property instead (or also) if that would work better.

[mara9642]

Description, comments, status, date entered, and date updated are all fields on the same table. I would like to copy each update to a new but related table as mentioned above. I'm just not sure how to set up the logic in Access to do this. Thank you!

[June7]

The logic to do what - create a new table or set up data entry? For the first can use simple query to retrieve the desired fields then copy/paste records to another table. Delete fields from original table. For data entry, build form/subform arrangement.

[mara9642]

Attached is a database that illustrates what I would like to do. Note: I built the attached database at home in Access 2016; because I can't post the work one due to company policies. My work system uses Access 2010

Hopefully this is a better description of what I'm trying to do:
I would like to save this record to table Archive. I would like to save fields: Description, Date Updated, EntryTable_ID
If a change is made to a new record then would I would like to save [Entry Table].[Date Entered] to the field
[Archive].[Change Date] instead of the [Entry Table].[Date Updated] field which should be blank at this point.
Also I would like to automatically update the [Entry Table].[Date Updated] field with the current date and time.
I've established a one-to-many relationship between the [Entry Table].[EntryTable_ID] and [Archive].[EntryTable_ID]
fields respectively. I need to incorporate an analogous feature for the [Entry Table].[Status] and [Entry Table].[Comments] fields.


Archiving Test.zip

Thank you again for the help.

[June7]

Don't really understand what you are trying to achieve. If your goal is to implement an 'audit trail', review http://allenbrowne.com/AppAudit.html

Still seems to me you just need to enter a new record for the new status. Status of what - whatever is in the Description field?

CopyObject is not the command to copy a record. Use INSERT SELECT action SQL.

CurrentDb.Execute "INSERT INTO Archive(EntryTable_ID, [Change Date], Description, Status, Comments) SELECT EntryTable_ID, Date_Entered, Description, Status, Comments FROM [Entry Table] WHERE EntryTable_ID=" & Me.EntryTable_ID

Advise you not use spaces in naming convention.

[mara9642]

I do want to implement and audit trail. Thank you, I just didn't know the appropriate terminology. This is very helpful. I'll play with it and then let you know if I have anymore questions!