We help IT Professionals succeed at work.

Error 7878 (another User ,,) - Form - Combo  SQL Server View

I have a form that if
   1) I change the value in a combo box (It has to be any combo on the form)
   2) maybe change other data (it makes no difference)
   3) Then move to a different record
   4) come back to the record and try to change data
   5) When I try to change the data I get Error 7878  - Another user edited this record and save the changes .....
   6) It happens every time - I've reproduced over and over

any help would be greatly appreciate.

   1) The form is hooked to a query that the recordsource is an SQL Server View
   2) I open the form (do this all the time) by setting the recordsource as something like SQLView where user ='LJG'
   3) The before and after update events of the combo only runs after the error
   4) The same problem occurs if it take out all the code in the form (Has Module = False)

Something I have tried
   1) on Form_Error I put the following code
        If DataErr = 7878 Then
            Response = acDataErrContinue            
        End If
   2) The above stops the error, but when you come back
       > pick a new item in the combo
          * The program sits with the open combo on the new Item you picked
          *  The program runs the above code (Response = acDataErrContinue )
          *  The program continues to sit with the open combo on the new Item you picked
          *  When you click on the new item a second time all works
       > Edit a text field
          * The program ignores the first keystroke and then everything is OK

Thanks again

Watch Question

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

First, is this a multi-user system ?
Or are you the ONLY person using the db ?



My Customer has this in a multi user system.  However in my development system I'm the only one in my office and I only have one copy opened.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Well, in that case ... this error (Write Conflict) usually happens because ... the current record (or possibly one nearby) is being updated by an independent process.  For example, you run some code that updates a field ... as part of whatever code routine is current being executed ...



Thanks for the thoughts - but I don't think it's the case - unless you see something I don't see

Just some facts
1) I got rid of all code in the form and still get the error.
2) If I wait for 2 minutes and come back to the record - I still have the problem
3) If you don't update a combo - and update a text box there are no problems (Combo only problem)

But I'm open to any thoughts


Thanks for trying
Database Developer
This scenario often quite occurs and, while it could be a genuine write conflict (this doesn't appear to be in this case) it is much more often a data type issue. (Or, more accurately, the reinterpretation/evaluation of certain data types in the ODBC translation.)

It can be due to a Bit field without a default (or just assigned Null by some other process, which vies with Jet/ACE’s understanding of a boolean, so preventing Nulls on Bit fields is also a good idea for use in Access linked tables) but, more often than not, it’s a floating point precision field (a Single or Double which has been upsized to a Float or Real, such columns can fail – simply due to the nature of their storage and inability to exactly compare every value.)

The solution is almost always to simply add a TimeStamp column to the table in question.
Access then uses that server controlled (automatically assigned) column to determine conflict status – and doesn’t attempt comparison with every field in the table (including the problematic columns mentioned).

Adding a TimeStamp and refreshing the linked table are really simple and worth trying regardless and all you need to do.
(But a default and disallowing Null for your Bit fields is helpful for linked ODBC tables!)

refreshing is as simple as the VBA line:
to update the locally cached schema of the server table.



Thanks so much for leading me down the right track.
The answer was refreshing the form itself.

I already had timestamps on all tables
I tried refreshing the main table
I tried refreshing the view
None of that worked - but you gave me the idea that refreshing might be the answer, and refreshing the form did it.


Just might help someone else that has the same problem.

Me.Refresh takes some time (in my case about 3 seconds) so I don't want to run it on every update.

The answer is the below code - with theses thoughts
1) If the problem occurs - the user just clicks again and it all works
2) The refresh form only runs when there is a problem - and solves the problem on all records of the form until the error happens again
3) if error Error 7878  - Another user edited this record and save the changes .....  happens for some other reason - if it happens twice on the same record - the user actually get's the appropriate error.

Hope this helps somebody.
--> in form Declarations
 Dim intFormErrorCount As Integer

On --> OnCurrent
     intFormErrorCount = 0
 On --> Form Error
 On Error Resume Next
    If DataErr = 7878 Then  'Error 7878  - Another user edited this record and save the changes ..... 
        If intFormErrorCount = 0 Then
            Response = acDataErrContinue
            'Debug.Print Form.ActiveControl.Name
            'Debug.Print Me.ActiveControl.Value
            Response = acDataErrDisplay
        End If
    End If

Open in new window