How to change date automatically on a change

[Andrewww]

Hi there,

I'd like to do something with Microsoft Access but I don't know how to do it.
I created a form that I linked to a table. There is about 12 columns in it, and one of them is called "Updated" with a date format in it.
What I'd like to do is whenever I do a change in any column of my form, it would update the date automatically in the "Updated" column when I close the form or when I click on another line.

Anybody can help me to do this?
Thank you!
Andrew

 

[SgtJarrow]

Open the form in design view....

In the afterupdate event for each of the column, put:

Me![Updated] = Now() ' Or Date() if you don't want the time

Any time you change any field, the updated field will be updated. The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Andrewww]

Thanks for your reply Robert.

I tried that and unfortunately, I get that error message:

Microsoft Access can't find the macro 'Me![Update] = Date()'
I tried to change the "Me" for "Domains" because my form and my table are called "Domains", same thing.

Do you know what the problem is?

Thanks
Andrew

 

[jebry]

Hi Andrew!

In the box where you typed Me![Update] = Date() click the drop down arrow and choose [Event Procedure]. Now click on the ... to the right. This will take you to the code window and that is where you need to type Me![Update] = Date().

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Junior1544]

I would suggest putting it in the on dirty of the form... that way you type it in once, and when the form becomes dirty(some value changes) it runs that code...

one thing to note, the on dirty event is only available in access 2k and up...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Andrewww]

James, I have Access 97, so I can't do your suggestion (thanks anyway!)

Jeff, I still get an error.

I tried what you said but I'm getting another error.

Option Compare Database
Option Explicit

Private Sub Remarques_Change()
Me![Update] = Date
End Sub

See after the Me![Update] = Date, the () is taken off automatically. It won't keep it.
When I try to run my form like that, I get a compilation error, Project or library not found (it may not be the exact error, my Access is in french and I'm translating it to you).

Thanks again for you help everybody!
Andrew

 

[SonOfZeus]

Update is a reserved word. In my access routine I have the following and it works fine.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If txtStatusFlag = "F" Or txtStatusFlag = "R" Then
txtStatusFlag = "N"
txtStatusDate = Now()
End If
End Sub

txtStatusDate - field on the form I'm setting the value for. It has a recordsource from the Job Table

 

[Andrewww]

Okay!
It's working with Now() instead of Date().
I changed my column name also.
Unfortunately, with Now() the result is the date AND the hour. I just want short date format MM/JJ/YY just like Date() does.
Is there any other way or word to write?
I'm one step away for the perfect result...!

Thanks
Andrew

 

[Junior1544]

look at format...

me!DateField = Format(Date, "Short Date&quot junior1544@jmjpc.net
Life is change. To deny change is to deny life.