Access 2007 Update Dates & Add new date

[Guest]

This is the event that is running & works as required:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_ERR
' set bound controls to system date & time
DateModified = Date
TimeModified = Time()
BeforeUpdate_END:
Exit Sub
BeforeUpdate_ERR:
MsgBox Err.Description, vbCritical & vbOKOnly, "ERROR NUMBER " & Err.Number
& " OCCURED"
Resume BeforeUpdate_END

End Sub
-----------
Every time any data is changed I get a date & time added.
What I want to do now, is create a date WHEN A NEW RECORD is created.
This should be in a new field that will not be changed.

I have had a play about but the date/time changes as the above.
I created a new field called CreateDate, this is what I wish to fill & not
to change.

 

[George Nicholson]

Add to your Form_BeforeUpdate Event:

If Me.NewRecord Then
CreateDate = Date
End If

HTH,

 

[Guest]

Actually, a better way would be to add the CreateDate field to the underlying
table, and set its default value to Now().

Also, why are you breaking up your modified date and time. It is much more
convenient to store this as a single field, use the Now() function to fill it
in. If you ever need just the date or time portion you can use the DateValue
or TimeValue functions.

HTH
Dale

 

[Guest]

Dale,
The date & time fields were put in separatly as that was the way I was
advised to do it.
Would your suggestion as use now, then be over written.

George / Dale
I am very green when it comes to procedures, but if either of you care to
edit my routine I will be pleased to try it. I assume I will need a new data
field.
Thanks for looking
Steve