date now stamp on field 2

[aspnetuser]

Is it possible to add a field that updates only when another field in the table is edited? Like a date/time stamp everytime the field is edited to DATE().

I want to avoid doing this from form level because i would have to make this update to 20 forms.

MS ACCESS 2002
Regards,

 

[lupins46]

Nope.

Only in forms (and code or just code on its own.)

 

[aspnetuser]

crap
so would i just to the after update
me![fieldname] = now()
me.refresh

 

[lupins46]

You need to use the Form_BeforeUpdate event procedure so that any change to the record results in the timestamp being updated (assuming this is what you want to do).

 

[RickSpr]

Actually, what you want is a trigger. Jet databases don't support them, but you didn't actually say it was a Jet table. If you're using Oracle or SQL Server, you can create a trigger for each field that will plug the timestamp in for you.

If you are using Jet, the BeforeUpdate is the way to go.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[aspnetuser]

Cool tip, I am using jet but have other sql apps out their where I could use triggers...

Why the before update and not the after update?

I need to field to be stamped everytime a specific field on the form is updated. after update seemed to work...

what the before?

 

[RickSpr]

AfterUpdate occurs after the record has already been saved. If you store the timestamp in the record after it has already been saved, the record will have to be saved a second time. That will happen automatically in most cases and you won't notice it, but it's inefficient.

The second save could actually cause a slight problem in some cases. For example, if you have a "weak validation" that you're doing in the Form's BeforeUpdate event (such as warning the user about an invalid field but allowing him to override it and save the record), the BeforeUpdate event will occur a second time for the second save and the weak validation will occur twice. That could be confusing to the user.

Note that setting the timestamp in the BeforeUpdate usually does no harm. If the record save is canceled by Access after you've filled in the timestamp (this could occur because of a key violation, for example), the timestamp won't have been saved, and you will simply overwrite it with a new timestamp the next time BeforeUpdate occurs. Just be aware that in this situation, the timestamp will no longer be Null after the first update cycle fails, so don't depend on timestamp=Null in your logic. This dependence rarely occurs in reality, though.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[julie67]

I have read these posts and i do not understand how to write an event procedure in the before or after update field. I am just trying to put a date stamp on the record if any field has changed.

I have a fmNames form and user can change Names, addresses, etc as well as add new records.

I don't understand exactly how to do this. It has been a lot of years since I have had any programming classes.

Any help would be great.

Thanks,
Jules

 

[lupins46]

You should really have posted a new Q.
Event properties are listed on the properties dialog for the form or for any control.
To show Form properties, one way is to use Edit>SelectForm and then click the properties toolbar button.
Click into the Beforeupdate property and you will get a Build button (...) to the right. Click this and then say you want to use Code Builder.
This will drop you into the Form_beforeupdate event procedure.

To add a timestamp which records the date of the latest change you use:

me.mytimestampcontrolnamehere= Now()
using your own conrol name, of course.

The Beforeupdate event is run whenever data changes are saved (immediately before the save, so that the timestamp data is added to the changes)

 

[julie67]

is the mytimestampcontrolnamehere the name of the field i am storing the date in?

 

[lupins46]

That would be OK.

 

[julie67]

I think it is working...

I went to the code builder like you said on the BeforeUpdate property in the Names form, and typed:

me.[TimeStamp]=Now()

where TimeStamp is the name of the field in the Names table and it worked. Cool beans!!!!

Thank you so much!!!