I am trying to out in a field that will automatically pull in the system date&time anytime any other field in the record is amended. I have tried "After Update" in the text box event field set with "Amended = (Now)" but that doesn't seem to work except on creation of new records. Any help appreciated. Thanks in advance

In the BeforeUpdate event of the Form put:

Me.Amended = Now()

Pat, I can't get that to work at all. Also I don't understand (sorry to say - new to this Access stuff) what the "Me." part of the formula is/means. I tried both typing in the formula and using the code builder; I also tried it both in BeforeUpdate and AfterUpdate and none of it worked. There must be something very simple I am missing.

Assuming that Amended is the name of the field in the recordsource, change the control name (Name on the Other properties tab of the properties dialog) to txtAmended. Then place the following code into the BeforeUpdate event of the FORM.

Me.txtAmended = Now()

Me. - tells Access that the variable name is a field on the current form/report. When the wizard builds a form, it uses the recordsource field name as the control name. This doesn't usually cause any problems. However, once you start to write VBA code, Access can become confused since it cannot always determine whether your code is intended to reference the recordsource field or the control that holds it. Giving them different names resolves this issue.

Sorry to say, it still does the same thing, i.e. nothing. I tried the code both in the BeforeUpdate and AfterUpdate (which logically makes more sense to me) fields and both using the Code Builder and just hand typing the suggested code into the Update boxes. Still no date/time in the Amended field when a record has been amended and saved.

I'll take a cue from Pat Hartman here and say do not place code that changes the record in the After Update event. You will send your code into a loop. (See Pat, I do listen!)

When you click on the Code Builder, your event should end up looking very similar to this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtAmended = Now()
End Sub

Then before you close the code window, go to Debug>Compile <somename> on your menu. See if any error messages crop up, then try it again.
All of this of course assumes that there IS a field on your form that shows the Amended date/time.

HTH,
David R

There is much confusion regarding the BeforeUpdate and AfterUpdate events. To most people, AfterUpdate seems the logical place to put this type of code and the BeforeUpdate event seems to imply that Access is prescient http://www.access-programmers.co.uk/ubb/smile.gif But the Before and After actually refer to the physical update of the underlying table rather than the updating that took place on the form. So in reality, the BeforeUpdate event is the LAST form event executed prior to the actual saving of data to a table and the AfterUpdate event is fired immediately AFTER data has actually been saved.

The reason that placing code that changes some field on the form in the form's AfterUpdate event puts Access into a loop is because it "dirties" the record causing Access to need to save the record again. That fires the BeforeUpdate and then the AfterUpdate events and the AfterUpdate event dirties the record again causing Access to need to save the record yet again ... you get the picture.

As to your non-working code, check all the field names again and make sure that the code is in the FORM's BeforeUpdate event.

Sorry Pat and other folks. The fault was all mine. The code works fine when YOU REMEMBER TO PUT IT IN THE INDIVIDUAL FIELDS! I don't know why but I had some type of mental block that enticed me to believe Access could somehow figure out if code had changed in some other field in a record when the code wasn't in the changed field. I see the error of my ways and thank you for your time and patience. I will try to think things through a little more BEFORE asking next time.

Ummm, you should have been able to access (no pun intended) the Form's BeforeUpdate event by bringing up Form's Properties (double click the grey box in the top left corner) and going to Events.

Of course, if you only intended to log changes for specific fields, then ignore me as it is too early in the morning anyway.

Cheers,
David R