Access 2010:  Write Conflicts and Explicit VBA Save Errors

shelldee
shelldee used Ask the Experts™
on
Hi everyone,

While testing, I am getting odd errors thrown from subs with explicit save code, after I get the 7787 error that's trapped in Form_Error. It seems like I'm getting the last error that was thrown (for anything), but sometimes it seems like it could be a legitimate error. (I guess I wondering if  the Me.Undo or the Me.Refresh in Form_Error could cause another error to be thrown).

A decompile/recompile seems to fix it -- so I'm guessing that I've forgotten to close the  form sometimes while I've been working with the code -- causing some scrambling.

My question is: should I ever be getting an error in the procedure that saves (like cmdSave_Click) after the Jet error is thrown?   (Optimistic locking)

Here's the typical Form_Error Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo HandleErr
'MsgBox "DataErr = " & DataErr

Select Case DataErr

    Case gERR_JETWRITECONFLICT_7787
        gMsgWriteConflictErr_Undo
        With Me
            .Undo
            .Refresh
        End With
        Response = acDataErrContinue

Open in new window

And here's  some typical Save code:
Private Sub cmdSave_Click()
On Error GoTo HandleErr

If Me.Dirty Then
    Me.Dirty = False
End If

ExitHere:
Exit Sub

HandleErr:
'MsgBox "cmdSave Error# " & Err.Number
Select Case Err.Number
    
    Case gERR_RECORD_DELETED    '3167
        gMsgCurRecDeleted
        Me.Undo     '<--- don't really need this unless I'm enable/disabling the Save button
        
    Case gERR_LOCKED    '3218
        gMsgLockedErr_Undo
        Me.Undo
        
    Case Else
        LogError Err.Number, Err.Description, conMod, "cmdSave_Click"
End Select

Resume ExitHere
Resume

End Sub

Open in new window


Thanks for any help with this :)
Shelley
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Architect / Application Developer
Top Expert 2007
Commented:
I have many places where I trap that error and a couple of others related to Write Conflict. I'm not sure you can really do an Undo and/or Refresh in the Form Error event.  With the WC error, all I do is display a friendly message to the user, explaining 'what is happening' - and basically to proceed to happy hour, then come back later an try again.

In general, any error that occurs in the Form Error event need to be resolved before continuing ... such things as invalid date if user is entering a date in a date/time field, some text that does not match an input mask, stuff like this ... as well as the WC error.

mx

Author

Commented:
Hi MX,
Thanks for helping, again :)

<<basically to proceed to happy hour, then come back later an try again.>>
ha ha :)

<<I'm not sure you can really do an Undo and/or Refresh in the Form Error event. >>
Yeah, I was thinking there might be some issues...
I've used the Undo, because there was some confusion when they need to escape out twice ... and it doesn't seem to cause problems at run time (famous last words, tho!). I was more worried about the refresh .. just in case there was caching or timing issues that I don't know enough about. I basically wanted to duplicate what Access does, but without the 3 choices... but I will definitely rethink this.

<<In general, any error that occurs in the Form Error event need to be resolved before continuing >>
Yes, I do a lot more error trapping than what's shown (in both subs) ... depending on what validation I set in the table or form properties, and what I'm doing in the control and form Before Update events.
Regarding multi-user errors, tho, I usually trap for 7878 in Form_Error, too,...as well as 3022 for multi-user duplicates. (If I can easily trap the 'viewing' user's dupes in the before update event, then I can give a 'multiuser' dupe msg that make a little more sense.)

Thanks MX :)

Shelley

PS -- Also, I meant to mention in the last thread how awesome your Import Reporting form/system looks!!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Sorry ... I got slammed yesterday and (temporarily) forgot to come back to this.

Did I actually answer your question ?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks for getting back to me, MX :)

What it comes down to, I think, is that I don't understand what's happening with Jet/Ace, the UI, and VBA with this error (7787, and to some extent 7878).

I don't have any problems with the Undo and Refresh code being in Form_Error if the save is thru the UI only... it's only when the save is initiated thru VBA.

I also haven't had any problems with 3167 or 3218 with either UI or VBA saves (and with Undo code (plus other code) in Form_Error) -- and 3167 & 3218 are throw in the save sub (and not Form_Error) if the save is initiated via VBA -- but 7787 & 7878, AFAIK, can only be trapped in Form_Error.

For 95% of my forms, this isn't an issue -- I could remove the Undo/Refresh code, and instruct the users to manually undo with the error msg.

But I have one form that is set up based on status fields in the form's recordset. So buttons, tabs, labels, etc are visible/locked/etc based on these statuses. I have a procedure that tests if the status has been changed by another user in various events (like the form dirty event) and reruns the form setup if it has. I wanted to be able to run this test when there is a write conflict -- just in case one of the status controls has changed -- but I need it undirtied and refreshed in order to do it.

I'm wanting to treat Form_Error like any other event where I can write code in reponse to something.. and it seems that I can... except for these darn Write Conflict errors :)

So... this may be a case where I would just like the answer to be what I want it to be...
...where I was kinda hoping that I was screwing things up by not being careful enough with the form 'mode' when I was working in code...

Anyways, if you have any further insight, I'm all ears!
Shelley
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Actually I do have a couple more comments, but I have to head out for a bit ... back later.

mx

Author

Commented:
Thanks MX,

I've been thinking...the test that I've been using to check statuses on the form compares a module level variable that's initiated in the form 'setup' procedure, to whatever is in the control -- to try to take advantage of the automatic refreshes without hitting the BE with another look-up. This is a single form pulling one record and loading subforms dynamically into one sfrm ctl, in the tab's change event. If the status is changed, then tabs may need to be hidden, and another subform loaded; or controls on the current sfm may need to be locked, etc. (Also, there are a couple of sfms that are 1-1, whose record could be deleted if there is a status change -- so I am also running set up code from the sfrm Error event for 3167.)

But I think that maybe I'm trying to use the Error event in a way that it wasn't meant to be used. (Although I'd still like to understand the WC error better.)

For the parent form write conflict issue tho, do you think that I should compare the status controls' values to a look-up in the form's before update event, instead?  And if there still happens to be a write conflict, then direct the user to undo and refresh the form manually? (There's a button on the form that will test and refresh the setup.)

Basically, should I be completely rethinking how I've been using the Error event for the multi-user errors?

Thanks for sticking with me on this -- esp since it sounds like your really busy :)
Shelley
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"but 7787 & 7878, AFAIK, can only be trapped in Form_Error."
That is the case at the Form level (UI).  However, if you are manipulating recordsets in VBA code, the of course you can trap those errors there also (which I do).

---
"What it comes down to, I think, is that I don't understand what's happening with Jet/Ace, the UI, and VBA with this error (7787, and to some extent 7878). "

"(Although I'd still like to understand the WC error better.) "

Well, I don't mean to state the obvious, but ... it pretty much means - for a variety of possible reasons - 'you' and another user(s) are trying to update the same record - or a record in a page (locked) of records - at the same time.

I personally do not see the WC errors as any big deal.  At the UI level (Form), I simply display a friendly message indicating what is happening, possibly giving the users choices as to what to do. I code, I have to decide what s/b done, because displaying a message may not be an option - 'depending'.

Take a look at the image the OP posted in this Q from yesterday afternoon Here

For whatever reason, different versions of Access have either display this message (via the Form Error event) or not (assuming you have not put in code to trap the errors). And actually, as far as Access messages go, this is one of the better messages.

So, what is it that you don't think you are understanding?

"since it sounds like your really busy"
You have no idea. I need help !!
btw ... thx for the nice comment about the report import module form.  Send me an email on the side, and I will post some even better ones, wherein the 'theme' (color) isn't necessarily emulating our intranet color scheme.

Meanwhile, I'm impressed by your digging into these somewhat advanced Access topics!

Meanwhile(2), should hit 75 today, tomorrow & Sun - and be great weather for the NASCAR Nationwide & Sprint Cup races at Auto Club Speedway Sat/Sun ... which will be a nice diversion from databases !


mx

Author

Commented:
Hi MX :)

<<"That is the case at the Form level (UI).  However, if you are manipulating recordsets in VBA code, the of course you can trap those errors there also (which I do).">>
I'm meaning at the Form level, only (not with recordsets).

Take a look at the image the OP posted in this Q from yesterday afternoon Here
The link worked, but I couldn't see an image, but I'm assuming that it's this one:
Access 2010 Bound Form Write Conflict MsgAnd what I'm wanting to do is:
  1) Give my own msg  by trapping 7787 and using acDataErrContinue,
  2) Choose 'Drop Changes' for them by issuing the Me.Undo,
  3) Refresh the data so that the viewing user can see the other user's edits by using the
      Me.refresh (since this is a single record).

This works fine as long as I save using the Ribbon Save-Record cmd, or Shift+Enter, or by clicking the RecordSelector -- if it's visible.

But if I save using the form's cmdSave button, I sometimes get these weird errors thrown at the Me.Dirty = False line, from the cmdSave_Click event -- after the 7787 error has been trapped in Form_Error and it's associated code has run.

I think that what I'll do is test with 2 Accde files, just to make sure that it's not some weirdness with the compile state.  

I also might try using Docmd.RunCommand acCmdUndo instead of Me.Undo, and acCmdRefresh instead of Me.Refresh in the Error event code.

<Edit>: I'm also going to try moving acDataErrContinue-- make it the first line instead of the last.

<<"So, what is it that you don't think you are understanding?">>
Maybe it's not so much not understanding, as it is frustrated... ;)
For other errors that can be trapped in Form_Error, Access gives a msg that the user can only respond to with "OK". Depending on the error, these can be trapped in a sub that saves via VBA (like 3167 or 3218) -- Form_Error isn't fired. Not so with the WC error (for bound forms -- not recordsets).
Anyways, I'll try the testing, and get back to you...:)

"<<You have no idea. I need help !! ">>
Cloning is my  solution...

"<< Send me an email on the side, and I will post some even better ones>>"
I'll try to send towards the end of the day.

"<<should hit 75 today, tomorrow & Sun ">>
Bahhh... it's -22 deg C here (-8 deg F) ... but I do hope that the races are fun...:)

Thanks again MX --
Shelley
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I see.  
RE:But if I save using the form's cmdSave button, I sometimes get these weird errors thrown at the Me.Dirty = False line, from the cmdSave_Click event -- after the 7787 error has been trapped in Form_Error and it's associated code has run."

Try this change below and see if it eliminates your issue with the WC errors gig.

In regard to this section of the code:
Change this:

If Me.Dirty Then
    Me.Dirty = False
End If

To this:

On Error Resume Next
Me.Dirty = False
Err.Clear
On Error Goto  HandleErr

RE:
"For other errors that can be trapped in Form_Error, Access gives a msg that the user can only respond to with "OK". These ones can be trapped in a sub that saves via VBA (like 3167 or 3218) -- Form_Error isn't fired. Not so with the WC error (for bound forms -"

Well, there is a subset of errors - that only occur, and can only be trapped -in the Form Error event - including WC errors, as well as Dup Index, Validation expression errors, input mask errors, and a handful of others (I've never been able to find a complete list).

So errors either that occur in the Form Error event are mutually exclusive for other errors, in that - they only occur in the Form Error event (or possibly in code like the WC errors).  And, with the exception of the WC errors (and maybe some others I'm not aware of), the user only has a choice of OK, and the condition that raised the error must be resolved before continuing - OR - the user can Undo the changes.

RE:
"I also might try using Docmd.RunCommand acCmdUndo instead of Me.Undo, and acCmdRefresh instead of Me.Refresh in the Error event code."

That really should not be necessary.  Me.Undo should work just fine, as well as Refresh.

I'm guessing ... that if you are going to do the Refresh gig, the user would definitely need to cancel (Me.Undo) first.

"Bahhh... it's -22 deg C here (-8 deg F) ...
A GIANT chill just came over me.  I'm heading outside to warm up !  Man, there is No Way I could live in that kind of weather. I don't know how you do it!

"but I do hope that the races are fun...:)"
Guaranteed ... but I'm a much bigger fan of NHRA - nothing quite like it !

mx

Author

Commented:
That's a brilliant idea!! Yeh!!

And if I have any errors that need trapping (from validation, etc) that aren't being ignored, I can check for them and redirect or whatever before the Err.Clear.
Thank you MX!!

Also, thanks for the information about Form_Error :)

(And it's warmed up to a sunny  -10 deg C  (14 deg F)... Most of us at one time or another wonder how -- or why -- we do it too!)

Thanks MX --hopefully I won't be bugging you for a while (with this one anyways...)
Shelley
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Actually, these are the kind of questions I like (and Jim too) ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Typo:
are mutually exclusive for other errors

are mutually exclusive from other errors

Author

Commented:
Hi MX :)

Just wanted to let you know that using inline error handling is working great -- in fact, much better than what I had because I don't need extra labels to Resume to. So thank you, thank you!! These errors had been driving me nuts...

I think that when I save in code, then undo in Form_Error, things behave in a manner similar to when Form_BeforeUpdate is cancelled -- and now I'm getting errors like 2001 or 3021 which make sense, and I can ignore them. The others...I still don't understand... unless there was something weird with the compile state ... but at least now I can log them, to see if it is ever happening at run time.
I had also run into this problem when I wanted to Undo a duplicate entry (on a unique index), so I will change the error handling for these forms as well.

Below is an example of what I did. This is on one of the 1-1 sfrms. (There's no Form_BeforeUpdate on this one -- so no other errors to trap or ignore):
Private Sub cmdSave_Click()
On Error GoTo HandleErr

If Me.Dirty Then
    On Error Resume Next
    Me.Dirty = False

    If Err.Number <> 0 Then
        Select Case Err.Number
        
            Case gERR_PREVOPCANCELLED, gERR_NOCURRENTRECORD '2001, 3021
            'Ignore
            Err.Clear
            On Error GoTo HandleErr
                
            Case gERR_RECORD_DELETED    '3167
                Err.Clear
                On Error GoTo HandleErr
                gMsgCurRecDeleted
                If mfStatusChanged Then
                    Me.Parent.Refresh
                    Call Me.Parent.SetupForms(False, True)
                End If
                
            Case gERR_LOCKED    '3218
                Err.Clear
                On Error GoTo HandleErr
                gMsgLockedErr_Undo
                Me.Undo
                
            Case Else
                'Log error without showing msg to user
                LogError Err.Number, Err.Description, conMod, "cmdSave_Click" _
                        , "Error at Me.Dirty = False line", False
                Err.Clear
                On Error GoTo HandleErr
        End Select
    End If
End If

ExitHere:
Exit Sub

HandleErr:
LogError Err.Number, Err.Description, conMod, "cmdSave_Click"
Resume ExitHere
Resume

End Sub

Open in new window


Private Sub Form_Error(DataErr As Integer, Response As Integer)
'There are no Required Flds or Unique Indexes
On Error GoTo HandleErr
'MsgBox "DataErr = " & DataErr

Select Case DataErr

    Case gERR_JETWRITECONFLICT_7787
        gMsgWriteConflictErr_Undo
        With Me
            .Undo
            .Refresh
        End With
        'TEST FOR STATUS CHANGE
        If mfStatusChanged Then
            Me.Parent.Refresh
            Call Me.Parent.SetupForms(False, True)
        End If
        Response = acDataErrContinue
        
    Case gERR_JETWRITECONFLICT_7878
        'Dirty is 'cancelled' when this error occurs
        gMsgWriteConflictErr_Undo
        gDisableButton Me.cmdSave, True
        Me.Refresh
        'TEST FOR STATUS CHANGE
        If mfStatusChanged Then
            Me.Parent.Refresh
            Call Me.Parent.SetupForms(False, True)
        End If
        Response = acDataErrContinue
              
    Case gERR_RECORD_DELETED    '3167
        gMsgCurRecDeleted
        'If the record has been deleted, then AcquiredBy has changed and this subform
        'needs to be unloaded, ie Parent set up needs to be rerun.
        Me.Parent.Refresh
        Call Me.Parent.SetupForms(False, True)
        Response = acDataErrContinue
              
    Case gERR_LOCKED    '3218
        gMsgLockedErr_Undo
        Me.Undo
        Response = acDataErrContinue

    Case gERR_CLOSINGCANTSAVE   '2169  default can't be saved msg
        'probably don't really need this one here
        Response = acDataErrContinue
    
    Case Else   '2113 (wrong data type) is the only expected error
        Response = acDataErrDisplay

End Select

ExitHere:
Exit Sub

HandleErr:
LogError Err.Number, Err.Description, conMod, "Form_Error"
Resume ExitHere
Resume

End Sub

Open in new window


Also, I have tended to think of 7787 and 7878 as Jet errors (as opposed to 3197 as a VBA error), but I think that they are more of an Access error -- so my constant naming is a bit off --(and kinda pointless since I've got the number in there...oh well...).

Anyways thanks again, :)
Shelley

PS: You know that my next question is going to be about how you'd overwrite a write conflict trapped in form error.....just kidding :)

PSS: I will get an email off by tomorrow for sure
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"PSS: I will get an email off by tomorrow for sure"

psss t ... holding breath ... (in 80 degree weather) ... after finishing tonight's WOD.

"as Jet errors (as opposed to 3197 as a VBA error), but I think that they are more of an Access error"
I'm pretty sure they are JET errors, since this occurs when JET is attempting to Write data.

So ... the only other thing I would mention about the new code is ... don't you want to let the user know when a WC error occurs ?

mx

Author

Commented:
Sorry that I haven't gotten back to you until now... been a tad bit crazy...I'm too fried to keep going tho.

<<I'm pretty sure they are JET errors>>
Ok,  good! :)

<<So ... the only other thing I would mention about the new code is ... don't you want to let the user know when a WC error occurs ?>>

There should always be a WC error msg:
Me.Dirty = False -->(WC Error)-->Form_Error-->Case 7787, gMsgWriteConflictErr_Undo, etc -->cmdSave_Click-->If Err.Number <> 0 Then Select Case code

But yeah, if you are talking about not showing the msg in the cmdSave_Click-- Case Else -- that is a bit risky. I didn't want them to have to deal with those other weird errors -- just log them if they happen -- but I should probably show them. It will probably come back to bite me if/when some other more legitimate error pops up. So thanks (again)  :)

Shelley

...and I just sent off a short email -- hopefully to the right address :)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Got it :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial