Access 2k Front-End With MySQL Back-End Write Conflict Question?????

I just converted my Access 2k back-end to MySQL and have run into this problem while entering data on a form in the Access Front-End.  This form is a Work Orders form which contain various fields relating to the work order (customer, address, date, called in by, assigned to, call taken by, etc.) and it has a sub-form linked using the work order id where the problems reported by the customer are entered (using a combo box) of available problems to select from.

If I create a new work order and enter/change any of the fields on the order (on the main form) I can save the record with no problem.  But, if I enter a problem for the order on the subform then try to save the order  or change any other field on the main form of the order I will get the "Write Conflict" error message ....

"This record has been changed by another user since you started editing it., etc. etc."  I then have the option to Copy to Clipboard or Drop Changes as Save Record is grayed out.

Any ideas why this is happening.  I can use the Access back-end tables an obviously this did not occur.


ET Sherman

LVL 19
Eric ShermanAccountant/DeveloperAsked:
Who is Participating?
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.

Just a thought, do u have any unique fields in your tables? Even a datetime field in there is good to have
Create one say CreateDate in your table, auto defaults to current date/time
Do u still get the problem
Eric ShermanAccountant/DeveloperAuthor Commented:
Hello rockiroads .... Thanks for the reply.

Yes, there is a PK in the Problems table (ID set to Auto Increment).

Problems Table


I think I see what may be causing the problem.  Based on the problem code selected (on the sub-form)  there is an After Update event to assign the work order to the proper department (Tech or Delivery) on the main form.  Therefore, after the problem is entered the main form is dirty probably.

I need to set the main form .Dirty = False after the update but for some reason it doesn't like that.

varDept = DLookup("[DEPT]", "PROBLEM_CODE", "[PROB_CODE]='" & Me.Combo4 & "'")
varRoute = Forms!dataentry_workorder!ROUTE_NUMBER

rst.FindFirst "[route_number]='" & varRoute & "'"
If rst.NoMatch Then
    If varDept = "01" Then
        If Not IsNull(rst!DRIVER_ASSIGNED) Then
            Forms!dataentry_workorder!Combo81 = rst!DRIVER_ASSIGNED
            Forms!dataentry_workorder!Combo81 = rst!DRIVER
        End If
    End If
    If varDept = "02" Then
        If Not IsNull(rst!TECH_ASSIGNED) Then
            Forms!dataentry_workorder!Combo81 = rst!TECH_ASSIGNED
            Forms!dataentry_workorder!Combo81 = rst!TECHNICIAN
        End If
    End If
End If

Forms!dataentry_workorder.Dirty = False      <------Doesn't like this

Eric ShermanAccountant/DeveloperAuthor Commented:
The error I get is Run-time 2101 - The setting you entered isn't valid for this property.

Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

I mentioned the datetime because I have seen MySQL write conflict errors (not experienced myself) and creating a timestamp field resolves it. When I last used MySQL, all my tables had a CreatedBy, CreatedDate, LastUpdatedBy, LastUpdatedDate so this could explain why Ive never had to use timestamp fields to resolve problems.

Looking at your code, I cant see much wrong with it. U are setting values on the mainform.
Im not sure how the locking exactly works with MySQL,Record loaded on mainform would typically lock the record. fact that u got record open then got something else updating it might be causing it
I have found MySQL does work differently to Access but ive been lucky Ive not had this error.
I could be mistaken about this locking strategy of MySQL

Have u tried this?




cant remember exact syntax offhand

Eric ShermanAccountant/DeveloperAuthor Commented:
Hello rockiroads ...

Tried everything including the TIMESTAMP to no avail.  I know there are a lot of issues that will arise once the back end is converted to MySQL but this one is kind of weird.

I will post this in case someone else runs into the same problem.  I think it's fair to close out this question as no solution was actually provided.

After much trying and testing it seems like if the field on the main form that's being assigned based on a value entered on the sub-form "did not"  change then the error would be presented.  As long as the employee being assigned the work order actually changed then it worked ok.  Therefore, I had to add some additional code to see if the value in the combo box on the main form is not the same as the value it will be replaced with.

Almost seems like if they are the same then MySQL starts editing the record but since there is nothing to change the form thinks the record is still being edited.

Strange but oh well.  The cost of converting!!!!


Hi ET,
sorry, I cant think of any other ideas. Ideally I need a environment like yours. I dont have mysql on my laptop anymore so couldnt verify. I could then see what the issue is. My thoughts are its down to locking. Subform editing record is another mysql thread.
This is the locking page from mysql - it may help u out or give u some ideas

I have no problems with the delete of question.

Some further reading (just some info to hopefully help ET - obviously not with this problem though)
I did a further digging around but couldnt find much
However I found this regarding timestamp field - something I mentioned earlier

Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks for the links rockiroads .....  The more the better it seems.

Yes, I do agree it's related to locking as I mentioned.  I the AfterUpdate code on the sub-form actually changes the value of the combo on the main form then I can simply force the .Dirty property to false and it work fine.  I only get the error when the AfterUpdate Event on the sub-form enters a value in the combo box on the main form and the value did not physically change from what it was.

For some reason the Access form thinks that record is still being edited in that case.  Kind of like MySQL starts editing the record but since there is nothing to change something stops but Access thinks it's still being edited.

I just updated the code to compare the two values and if they are the same basically "do nothing".

Much appreciated.


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
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
MySQL Server

From novice to tech pro — start learning today.