BeforeUpdate triggering twice due to updating bound field

Question

When an update is made, this application displays a prompt saying something like "You have made an update, do you want to save or not". When they click 'Yes', validations are performed and if they all pass, the 'Last Updated' and LastUpdated By' fields get updated as the last step in Before Update.

What is happening is the "save or not" prompt is displaying twice. After stepping through the logic, the BeforeUpdate event is being triggered twice. The only reason I can deduce for this is that my updating the 'Last Updated' fields (which are bound fields) is causing the triggering. The re-triggering happens upon running DoCmd.Close statement.

Does anyone know how I can update these two fields without re-triggering the Before Update event and all my validations?

Answer 1

I doubt that is the case. I use the same logic in my apps and I'm not seeing that behavior.

Try using the "reduction" method of debugging: comment out chunks of possibly irrelevant code until the problem goes away.

---

-Tom. Microsoft Access MVP

Answer 2

Like Tom, I don't experience that behaviour in similar contexts.  For the event procedure to be executed twice it would be necessary for the record to be saved before the values are assigned to the LastUpdated and LastUpdatedBy columns and/or some other changes to data are made.  You therefore need to check the execution of the code to see why it is being executed twice, if indeed that is the case.  Before selectively commenting out blocks of code as Tom suggests, I'd recommend that you run through all of the code first.  To do this set a breakpoint at the start of the BeforeUpdate event procedure and then edit a record.  You'll then be able to step into the code line by line with the F8 key.  This should help you to detect why the unexpected  behaviour is occurring, and point you to which possibly offending code can temporarily be commented out.

For an example of the use of the BeforeUpdate event procedure to get user confirmation of changes to a record, and then datetimestamp the record if the user confirms the changes, take a look at ChangedRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes an option to open a form to confirm changes to data.  Note, however, that the primary purpose of this demo is to illustrate how to detect that the values of data have changed, or will be changed rather than merely updated.  A record can be updated without any actual change being made to the values of data.  In the demo such changes are detected by code in the basChangedRecord and basRecordWillChange modules.

---

Ken Sheridan, Stafford, England

Answer 3

Another suggestion: set a breakpoint at the top of the function, then inspect the call stack.

---

-Tom. Microsoft Access MVP

Answer 4

My network's URL filtering database wont allow me to access any onedrive.live.com site:

"Your requested URL has been blocked by the URL Filter database module of McAfee
Web Gateway. The URL is listed in categories that are not allowed by your
administrator at this time."

Answer 5

My network's URL filtering database wont allow me to access any onedrive.live.com site

What people generally do in that situation is download the file at home, and either email it to their work address or transport it on a portable medium.  The complete code is too extensive to usefully post here, as I think you'd find it difficult to follow out of context of the application.

---

Ken Sheridan, Stafford, England

Answer 6

This is Close button logic. When I step through the code Me.Dirty is True at the top, after I set Me.Dirty = False, it is still True. It then drops through to DoCmd.Close, immediately after this statement it goes back to BeforeUpdate for a second time.

It's several years since I wrote all this, so I'm not sure what everything is doing. Some of the comments don't seem to make sense.

How do I inspect the call stack?

Private Sub butClose_Click()
   ' Validate form before attempting to close (DMC 6/7/2012)
   ' Resume next needed:
   ' - In case Form_Update is cancelled
   ' - Or in case Docmd.Close fails
   On Error Resume Next  'NEEDED !!!
   ' Force validations if form dirty
   If Me.Dirty Then
      Me.Dirty = False  'Triggers Form_BeforeUpdate
      If booCancel Then Exit Sub  'Validation errors
   Else
      If strOpenArgs = "N" Then  'Creating new case
         If CheckFields() Then
            booCancel = False 'Allow exit (DMC 11/27/2013)
         Else
            booCancel = True 'Disallow exit from form
            Exit Sub
         End If
      End If
   End If
   ' Special checks when new case closed
   If strOpenArgs = "N" Then
      If Not CheckCloseNew() Then
         booCancel = True 'Disallow exit from form
         Exit Sub
      End If
   End If
EX:
   DoCmd.Close acForm, Me.Name
End Sub

Answer 7

I stepped through the code when it works (runs Form_BeforeUpdate just once) and when it doesn't work (runs Form_BeforeUpdate twice).

When it works, immediately after BeforeUpdate exiting, it runs AfterUpdate. Me.Dirty is False. Then subsequently it follows different logic paths and does not reinvoke BeforeUpdate.

When it does not work, immediately after BeforeUpdate exiting, it does not run AfterUpdate but instead goes back to the Close command button event procedure. Me.Dirty is True. It attempts to close the form but then shoots back to BeforeUpdate again.

Any clues what is happening based on this?

Answer 8

When it does not work, immediately after BeforeUpdate exiting, it does not run AfterUpdate but instead goes back to the Close command button event procedure. Me.Dirty is True. It attempts to close the form but then shoots back to BeforeUpdate again.

Any clues what is happening based on this?

Hi ATGNWT,

What is the condition to let the code go to the Close command button? Is there any error handling that sends it to that?

You could disable error handling, and see in the debugger which line, and what condition, causes the problem.

Imb.

Answer 9

I would not rely on the form's BeforeUpdate event procedure alone to undertake the validation, but instead move the validation code to a Private function declared as Boolean in the form's module.  The function can then be called from both the close button's Click event procedure, and the form's BeforeUpdate event procedure, e.g., in simplified form, for the former:

    If ValidationFunction() = True Then
        ' code to datetimestamp record goes here
        DoCmd.Close acForm, Me.Name
    End If

and for the latter:

    If ValidationFunction() = False Then
        Cancel = True
    Else
        ' code to  datetimestamp record goes here
    End If

However, this does not cater for a user closing the form other than by the command button, so you would also need to cater for this by setting the return value of the Unload event procedure's Cancel argument to True, which is usually done by assigning a value to a module level variable of Boolean data type if the validation criteria are not met.

Another approach, which I've found reliable and have generally adopted in most similar situations is to force the user to save the current record by means of a command button, before the form can be closed (also by a command button)  or moved to another record.  You'll find an example in SaveDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file Save, Undo and Close Form button's are enabled/disabled in the form's module on the basis of the state of the current record in the form.  Closing the form by other means is also prevented by code in the form's module.  This demo also includes validation at control level, so, while the BeforeUpdate event procedure does include validation code, this will not normally be brought into play.

---

Ken Sheridan, Stafford, England

Answer 10

This is the code in my close button event procedure that triggers BeforeUpdate (the complete procedure is in an earlier post):

 If Me.Dirty Then
      Me.Dirty = False  'Triggers Form_BeforeUpdate <===================
      If booCancel Then Exit Sub  'Validation errors

So when the code is not working, it comes back here and Me.Dirty is still true

When the code is working, it runs AfterUpdate and Me.Dirty is false

I have a couple of procedures containing 'On Error Resume Next' and my current suspicion is that one of these may be masking a real error, so I will comment those out and see if anything is revealed.

Looks to me like the update is never being done.

Answer 11

I commented out two 'On Error Resume Next' statements and it revealed this error. This happens at the last statement in BeforeUpdate when I click F8 (continue) in Debug.

Any ideas what would cause this?

Answer 12

As you are currently ignoring errors, I would guess that an error is being raised by the Me.Dirty = False line, but not handled.

When undertaking in-line error handling the assumption is that a known error is anticipated on specific line.  The line in question should therefore be followed by a Select Case statement in which, on the basis of the error number, action appropriate to the error can be undertaken.  This can be simply no action where appropriate, but should include a Case Else statement to handle any unanticipated error, usually by aborting further code execution and informing the user of the error

For now, do as you intend and comment out the On Error Resume Next line to see exactly where any error occurs as you step into the code following a breakpoint.

---

Ken Sheridan, Stafford, England

Answer 13

Your last post crossed with mine.  What is the line on which the error occurs?

---

Ken Sheridan, Stafford, England

Answer 14

The error occurs when the cursor is on 'Exit Sub' in BeforeUpdate, and I press F8 (continue).

Answer 15

The error occurs when the cursor is on 'Exit Sub' in BeforeUpdate, and I press F8 (continue).

Hi ATGNWT,

When you arrive on Exit Sub or End Sub, continue to press F8 (not Shift-F8) to see what happens in the rest of the calling routine. Also disable error code (if necessary) in this calling routine. Somewhere towards the end of the program you will find the causing problem.

Imb.

Answer 16

The error occurs when the cursor is on 'Exit Sub' in BeforeUpdate, and I press F8 (continue).

The only thing I can do at that point is to click [End] [Debug] or [Help] in the error message window.

I have already explained what happens if I continue; BeforeUpdate exits but Me.Dirty is still True, so when I attempt to close the form it runs BeforeUpdate again.

Answer 17

I would still recommend that the save and close operations be separated, forcing the user to specifically save the current record before they are able to close the form, as my demo illustrates.  All validation can then be done independently of the close operation.  Over the years I've used this approach on numerous occasions and found it reliable and easy to implement.  The CustomNumber demo in my same OneDrive folder uses it for instance to ensure that a form is not closed after a conflict in a multi-user environment has generated a key violation error, which would result in loss of data otherwise.

---

Ken Sheridan, Stafford, England

Answer 18

I understand what you are saying, and it does seem a better design than what I have, but we are converting most of our old MS Access applications to web applications so I just need to keep this one running until then. It's been running for years with no problems. I just want to diagnose and fix this issue making the minimum number of changes necessary.

There must be something unusual about the data entered for this record as this issue had never surfaced before in thousands of records that have been input. I do have all the validations in a separate module, which is called from different places, but in this instance all the validations are passing. It just seems that for some reason it is unable to perform the update with the data that it has.

Answer 19

I have an interesting discovery. I started thinking it could be something to do with the table record, since in this instance I am updating an existing record. So I went to the table and updated one column, inserting a '1' where there was previously a null. This is what happened as soon as I updated the column. So do I assume the table is corrupt and do a compact/repair?

Answer 20

A compact/repair on the back-end file made the problem go away.

Answer 21

This is the code in my close button event procedure that triggers BeforeUpdate (the complete procedure is in an earlier post):

 If Me.Dirty Then
      Me.Dirty = False  'Triggers Form_BeforeUpdate <===================
      If booCancel Then Exit Sub  'Validation errors

So when the code is not working, it comes back here and Me.Dirty is still true

When the code is working, it runs AfterUpdate and Me.Dirty is false

I have a couple of procedures containing 'On Error Resume Next' and my current suspicion is that one of these may be masking a real error, so I will comment those out and see if anything is revealed.

Looks to me like the update is never being done.

Hi,

You use "BeforeUpdate" Event for "Me.Dirty = False", unless it's your intention to save the edited record, you'll have to make sure all your fields/textbox are validated for correct input. There is one or more possible cause for this. Like say, a Textbox when edited or changed by another code.