Solved Error on Button On Click When I Have an After Update Event On The Same Form

[regnewby2465]

On Form called CAD_CallEntrySplitF I have a button named NowAndSaveB which has an Event On Click. I have two other buttons on the same form that have an Event On Click. They all work fine until I put an After Update event on the form itself. The code for the After Update event on the form is:

'-----------------------------------------------
Private Sub Form_AfterUpdate()

On Error Resume Next

    If IsNull([DateTimeRcvd]) Then
        [DateTimeRcvd] = Now()
    End If

End Sub

'--------------------------------------------------

Here is the error.

If I remove the form's After Update event, everything works fine. Any advice? Thanks in advance. Mark

 

[theDBguy]

What is the error message?

 

[regnewby2465]

It errors on Private Sub NowAndSaveB_Click, more specifically the DoCmd.GoToRecord , , acNewRec

 

[theDBguy]

It errors on Private Sub NowAndSaveB_Click, more specifically the DoCmd.GoToRecord , , acNewRec

Yes, but I was wondering what the error message say. Does it say you can't go to a new record at this time?

 

[regnewby2465]

Run-time error '-2147352567 (80020009)':
The data has been changed.

 

[theDBguy]

Run-time error '-2147352567 (80020009)':
The data has been changed.

I see. Why did you use On Error Resume Next? If you comment it out, what error message do you get?

 

[The_Doc_Man]

When you create a form bound to a record, there is a moment of data synchronization between the form and the record that is signaled as the Form_Current event. It says that whatever is in the bound record, the form currently reflects it. I.e. the data in the form is "current."

As you make changes to bound controls, the form and underlying record diverge from each other. Some actions that you take will make them become current again. The "Save Current Record" action occurs manually OR if you are set up to allow an auto-save on navigation, but that auto-save IS an option that you didn't have to take. (It's a cursor setting that says rather than navigate, your cursor moves back to the first field.)

If you have the "cyclic" cursor setting then that auto-save is not available then you can't so easily navigate away from the form while it is dirty. It got dirty when you changed a bound control in the AfterUpdate event.

This link explains the order of events on a form. Scroll down to the heading that includes "updating data in records" and check for the events - including one event that doesn't appear.

Order of events for database objects - Microsoft Support

support.microsoft.com

Access calls your event entry points if you declare the event code. The BeforeUpdate event occurs before Access writes back the changes you have made on the form. The AfterUpdate event occurs when it is done making the changes. A couple of events later, the Current event kicks in to let you know that all changes have settled.

Here is where that error occurs. Your AfterUpdate event dirtied the form and somehow you are not allowing automatic updates. So to navigate to a new record at that time, you have to first SAVE the current record (again) because you dirtied it in the AfterUpdate event. It is legal to move to a new record - but not if you don't save the current one first. And not if you dirty it after you save it but before you try to navigate.

In this case, I am not sure that the Form_Current event will occur if you dirtied the form in AfterUpdate because the form is still dirty (or dirty again) after that event routine.

Now, your actual complaint is "They all work fine until I put an After Update event on the form itself." Move that code to the BeforeUpdate event and you will see things working a LOT better.

EDIT: I see June7 ended with the same advice I ended with. But I like to explain WHY things work that way. Forgive me if I get long-winded at times. It's a remnant of the teacher that I was in several jobs, none of which were actually in a learning institution.

 

[arnelgp]

Run-time error '-2147352567 (80020009)':
The data has been changed.

you got error because you Already has the Record Opened in your form and you are Updating the Same record via Update Query?
that means, the record you are "viewing" is not current, since you already updated it non-visually.
what i recommend is not to do this Query update, but you updated it using the Form's Recordset.

 

[Pat Hartman]

When you dirty a record in the form's AfterUpdate event, you usually put the form into an infinite loop. The AfterUpdate event runs only if a record has been saved and it runs after the BeforeUpdate event. When you populate the date field with Now() you dirty the record again and that forces Access to have to run the BeforeUpdate event, save the record and then it runs the AfterUpdate event and your code dirties the record again forcing Access to run the BeforeUpdate event, save the record, and then run the AfterUpdate event where your code dirties the record again forcing Access to run the beforeUpdate event ----- Do you get the picture???

Luckily, current versions of Access are able to recover when Access recognizes the recursion in the call stack and Access ends the loop gracefully so you don't even know you caused one. Older versions used to just freeze and the screen would flicker. You would need to reboot to break out of the loop. Also, this particular piece of code doesn't actually cause an infinite loop because of the If statement. So, the loop only happens once. The record is saved with INCORRECT data. Then the AfterUpdate event runs and corrects the data. The data changes causes the BeforeUpdate event to run, then Access saves the record and calls the AfterUpdate event again but your code doesn't dirty the record again because it now has a value.

The bottom line is, NEVER change the current record in the Form's AfterUpdate event. Your validation code needs to go into the Form's BeforeUpdate event so you can validate a record before it gets saved and cancel the save if there are any errors. In this case, you can just provide the default and let the BeforeUpdate event complete naturally. You don't have to cancel the event to prevent the save from happening.

Uncle Gizmo and I recently made a video to talk about a sample database I made to explain the Form's BeforeUpdate event. I think you might benefit from watching it.

Bad Data is Bad for Business | Access World Forums (access-programmers.co.uk)

 

[regnewby2465]

Thank you again Pat....You answer my questions pretty plainly so I can understand being a newbie to vba. I will watch the video and mark this thread solved.

 

[Pat Hartman]

You're welcome again