Update Date Field (for Mod Date)

[bluemoon9]

I would like to make my "MODDATE" field to be updated to the current date when user edit record by "Click on Save" button
Here is my code for the "Save" button:

Private sub Save Click()
MsgBox "Record has been updated successfully!", vbInformation, "RECORD HAS BEEN UPDATED!"
    Me.MODDATE.Value = Date
    DoCmd.Close acForm, "frmReview", acSaveYes
End Sub
 

In the tblReview, I have a field call "MODDATE", and I set this field to have a default value =Now(). It works well when user enter new record, but not when user update existing record.
I also tried: me.MODDATE.Value=Now() and also me.MODDATE=Now(),
they all me error which completely stops my database application.

Can someone please help?
Thanks!
bluemoon

[Megalog]

Interesting.. I use pretty much the same thing throughout my database, on the form's 'BeforeUpdate' event.

me.Modified.Value = format(Now(), "m/d/yyyy  h:mm:ss AM/PM")

In my table, the field is set as a Date/Time, with NO preset format or default value.
My only guess is that maybe you have a format set which is conflicting with what Now() is returning from your form? Or, your field is set as the wrong Data Type.. (text instead of Date/Time?)

Add some error handling to your save button event and see what it tells you.

-Edit: Just noticed this is in the wrong area, should be moved to the Answers section.

[NeoPa]

Bluemoon, You don't say in what way your code is failing. That would be useful.

The code there seems workable (assuming the MODDATE control is properly bound to the ModDate field in the underlying table).

PS. Use Date() for a simple date value, but Now() for a date value which includes the time element.

[NeoPa]

Megalog, I recommend losing the Format() part of your code. This converts the date value to a string, which then only needs to be converted back automatically by Access. This won't fail, it is simply unnecessary (performance won't be noticeably different).

PS. Thanks for the heads-up about the wrong section. A better method for that would be the site's Report facility (top right of each post - other than your own). This will trigger an email to inform someone of the problem. Appreciate your help anyway.

[missinglinq]

A Couple of points:

First, of course, you don't really need a "Save" button in Access. Moving off of the record or closing the form will save the record.

Secondly

Close acForm, "frmReview", acSaveYes

isn't doing what you think! It's saving any changes made to the design of the form; it isn't saving the record.

And last, I wonder if NeoPa's statement

assuming the MODDATE control is properly bound to the ModDate field in the underlying table

has pinpointed the problem. You have a MODDATE field in your underlying table, but do you actually have a MODDATE control (textbox) on your form?

Linq ;0)>