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

Problem:
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.
LJG

Facts
   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
Ljg


   
LVL 2
LJGAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
First, is this a multi-user system ?
Or are you the ONLY person using the db ?

mx
0
LJGAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ...

mx
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

LJGAuthor Commented:
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
0
LJGAuthor Commented:
Thanks for trying
0
Leigh PurvisDatabase DeveloperCommented:
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:
CurrentDb("LinkedTableName").RefreshLink
to update the locally cached schema of the server table.

Cheers.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LJGAuthor Commented:
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.
Thanks!
0
LJGAuthor Commented:
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.
LJG
--> 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
        Else
            Response = acDataErrDisplay
        End If
        Me.Refresh        
    End If

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.