Date Created and Date last modified

[Detectiveclem]

Can anyone assist.



I have a Form which has four fields which are causing me some issue, one called 'DateModified', one called'TimeModified' one called 'DateCreated' and finally one called 'TimeCreated'.

I have managed to auto populate all four fields with the date and time, however when I return to the record in the Form and make any changes, the DateCreated & TimeCreated fields change and mirror the new DateModifiied and TimeModified fields.

Whilst I want the Modified fields to update, I don't want the create date and time to change. These Create fields should always show the original date/time when the record was first created.

Any help will be greatly appreciated.

Thanks

[orange]

Instead of separate Date and Time fields, I recommend you use the Now() function which includes date and time.
For example:
?Now()
29-Aug-18 9:06:48 AM


I don't think you would want these controls ("fields") bound to the underlying table since any change will update your table field values.

Please tell us more about what and why exactly are you recording these dates and times.
It would seem that once the Created info is recorded, you would have code/logic to prevent it from being changed.
Good luck with your project.

[Detectiveclem]

Thank you orange, I did consider using the now function, but for my purpose the seperate fields look better.

The reason I want both the 'Create' and 'Modified' Fields, is because I need to visably show when a new record was created and also when it was last updated. I am not using the dates/times for any other reason.


As a newbie to Access are you able to lead me through the code/logic I require to prevent to 'Create' fields from changing after the record is first created?

Thank you.

[davegri]

You haven't said much about the code updating the fields, but consider including the .newrecord property for the form.

If Me.Newrecord then 
    'update created fields
    'update modified fields
else
    'update modified fields
end if

[Detectiveclem]

Hi Davegri,

Thank you, I will try this, do I use this code in the Form or in the fields?

Cheers

[davegri]

I will try this, do I use this code in the Form or in the fields?

As I said, I don't know how you are set up to add/edit your tables, but this code would be included in the code that creates the new records.
I don't have enough information to be more specific.

[Minty]

If you set the Default values for the created date / times as Now(), simply don't ever update them.
Lock them down so they can't be edited. In the BeforeUpdate event of the form set the Date / Time Modified to now() before saving the record. Something like

If Me.Dirty Then
   Me.DateModified = Now()
   Me.Dirty = False
End If

As others have said you only need one field for each of these.
If you need the displayed separately just duplicate the field and set the display format to only display the Time value / date value accordingly, and lock them all from direct editing.