Problem with Auto-Updating a Date Field

[UncleBulgaria]

I have a database that has data input on one main form that contains various sub-forms for different data types in various tables, all connected via an ID code.

On one of the sub-forms, I wanted it to automatically collect a record updated date. I have added the relevant field to the table behind the form, and have added the field to the relevant sub-form (albeit not visible). I perhaps should also mention there may be more than one record per ID code. The sub-form worked perfectly before I started all of this and added the following VBA code:

Private Sub Form_AfterUpdate()
Me.Record_Amend_Date = Now()
End Sub
​

Now when I amend data it will not let me move to the next record, and if I try to close it throws up a message box saying that:

'You can't save this record at this time. Microsoft Office Access may have encountered an errorwhile trying to save a record. If you close this object now, the data changes that you have made will be lost. Do you want to close the database object anyway?'
​

If you click 'Yes', the form closes and when you check the table the data is there and the amend date is showing correctly.

Does anybody know why it is doing this and how to get it so I can amend data and then move to the next record?

Thank you for your help.

Regards,

Robert.

 

[boblarson]

I, personally, would move the

Me.Record_Amend_Date = Now()

into the BEFORE update code as I've seen before it run into a perpetual loop by putting it on the After Update event (although some don't have that problem). It happens because the form is updated and then you add that date/time and it then updates again and then the time has changed so then the update occurs over and over and over again.

 

[RuralGuy]

You want to add time stamps in the BeforeUpdate event and not the AfterUpdate event. Doing it in the AfterUpdate causes an infinite loop.

 

[UncleBulgaria]

Thanks for that, works perfectly now. I hadn't thought about the looping!

Robert.

 

[RuralGuy]

Glad we could help.