Solved

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

Posted on 2007-03-21
10
429 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 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

21 Experts available now in Live!

Get 1:1 Help Now