Solved

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

Posted on 2007-03-21
10
431 Views
Last Modified: 2012-06-27
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.

Thanks,

ET Sherman




 
0
Comment
Question by:Eric Sherman
  • 4
  • 4
10 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 18767320
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
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 18767395
Hello rockiroads .... Thanks for the reply.

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

Problems Table

ID
Work_Order
Problem_Code


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
Else
    If varDept = "01" Then
        If Not IsNull(rst!DRIVER_ASSIGNED) Then
            Forms!dataentry_workorder!Combo81 = rst!DRIVER_ASSIGNED
        Else
            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
        Else
            Forms!dataentry_workorder!Combo81 = rst!TECHNICIAN
        End If
    End If
End If

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


ET
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 18767470
The error I get is Run-time 2101 - The setting you entered isn't valid for this property.


ET
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18767552
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



0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 65

Expert Comment

by:rockiroads
ID: 18767576
Have u tried this?

Me.parent.dirty

or

me.parent.form.dirty

cant remember exact syntax offhand


0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 18773443
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!!!!

Thanks,

ET
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18779080
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 http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html


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
http://www.thescripts.com/forum/thread73999.html


0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 0 total points
ID: 18783154
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.

ET
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run Time Error 3075 15 45
Newbie needs help printing from a form. 10 20
Continuous form : Prefilled Data 7 18
subform is not filtered by link field 8 19
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now