none
Write conflict... RRS feed

  • Question

  • I have created a form using an outer query, so that for a given order I can see all offered products, not just the ones already ordered.  I want my users to be allowed to enter/change the quantity.  If there is no current value, I will be inserting a record, if there is an existing value, I'll be updating a record.  My Default View is Continuous Forms.  I've created code to handle this in the QTY after update event.  This works beautifully, until I move to the next record, at which time I get a Write Conflict error.

    Any suggestions?


    Thanks for your help!!

    Wednesday, August 23, 2017 5:53 PM

Answers

  • Hi,

    Glad to hear you're making good progress. Here are the steps I think we need to take:

    1. Store the values for the UPDATE query into memory (variable)

    2. Store the ID of the record into memory (bookmark)

    3. Cancel the event and Undo the form changes

    4. Execute the UPDATE query

    5. Requery the form

    6. Navigate the form to the previous record using the ID (bookmark)

    Hope it helps...

    • Marked as answer by plynton Sunday, August 27, 2017 1:11 PM
    Saturday, August 26, 2017 4:14 PM

All replies

  • You may have to show us your code. Which event are you using? If the AfterUpdate of the Qty field, then you might try using the BeforeUpdate event so you can Cancel it when you actually update the table in the background.

    Hope it helps...

    Wednesday, August 23, 2017 5:58 PM
  • Merci!

    One followup - when you say 'Cancel it' what is 'it'?


    Thanks for your help!!

    Wednesday, August 23, 2017 6:03 PM
  • Here's the code.  Works fine - inserts/updates as expected until I try to move to a new record - at which point I get the Write Conflict error:

    Private Sub Qty_AfterUpdate()
    On Error GoTo errhandler
        If Me.Dirty Then
            ordSQL = "INSERT INTO tblOrderDetail (OrderID, ProductID, ProductName, Quantity, RetailerCost) VALUES (" & _
                Me.txtOrd.Value & ",'" & ProductID.Value & "','" & ProductName.Value & "'," & Me.Qty.Value & _
                "," & Me.RetailerCost.Value / Me.CasePack.Value & ");"
            CurrentDb.Execute (ordSQL), dbFailOnError
        End If
        Exit Sub
    errhandler:
        If Err.Number = 3022 Then
            updSQL = "UPDATE tblOrderDetail SET Quantity = " & Me.Qty.Value & _
                ", WasChanged = True WHERE OrderID = " & Me.txtOrd.Value & " AND ProductID = '" & Me.ProductID.Value & "';"
                Debug.Print updSQL
            CurrentDb.Execute (updSQL)
            
        End If
        
    End Sub


    Thanks for your help!!

    Wednesday, August 23, 2017 6:42 PM
  • Merci!

    One followup - when you say 'Cancel it' what is 'it'?


    Thanks for your help!!

    Hi,

    If you use the BeforeUpdate event rather than the AfterUpdate event, there is an argument allowing you to cancel the update, so you can freely update the table outside of the form, like you're doing now.

    The problem with your code in the AfterUpdate is you're trying to update the table from two locations: the form and your code. You probably can't update the form because the data is "read only," so there's really no need to use the AfterUpdate event because without the code to update the table, you'll probably get a different error anyway, correct? Otherwise, we won't need to use code, right?

    So, to cancel the update in the BeforeUpdate event, you simply use the following line:

    Cancel = True

    Hope it helps...

    PS. Sometimes, just canceling the event may not be enough. You may have to "undo" the change as well. So, you might have to do either something like this:

    Me.Undo
    Cancel = True

    or something like this:

    Me.Qty.Undo
    Cancel = True

    Cheers!


    • Edited by .theDBguy Wednesday, August 23, 2017 6:56 PM
    Wednesday, August 23, 2017 6:54 PM
  • Thanks again.

    The issue now is that when I include the 'Cancel = True', I can't move on from the field.

    So, at the end of the day, I want to code my update/insert - and then force Access to ignore my form update.

    Is there another approach to this?  


    Thanks for your help!!

    Wednesday, August 23, 2017 7:03 PM
  • Can you post the new code please? Thanks.
    Wednesday, August 23, 2017 7:12 PM
  • The After_Update code is above.

    I then added 'Cancel = True' to the Before_Update event.


    Thanks for your help!!

    Wednesday, August 23, 2017 7:13 PM
  • The After_Update code is above.

    I then added 'Cancel = True' to the Before_Update event.


    Thanks for your help!!

    Ah, what I said was to move your code to the BeforeUpdate event and not use the AfterUpdate event. So, I was expecting to see a full code in the BeforeUpdate event and nothing in the AfterUpdate event.

    Hope it makes sense...

    Wednesday, August 23, 2017 7:25 PM
  • OK, here's the new code.

    This works (inserts/updates my data), until I move to a new record - I then get the Write Conflict error.

    Private Sub Qty_BeforeUpdate(Cancel As Integer)
    On Error GoTo errhandler
            ordSQL = "INSERT INTO tblOrderDetail (OrderID, ProductID, ProductName, Quantity, RetailerCost) VALUES (" & _
                Me.txtOrd.Value & ",'" & ProductID.Value & "','" & ProductName.Value & "'," & Me.Qty.Value & _
                "," & Me.RetailerCost.Value / Me.CasePack.Value & ");"
            CurrentDb.Execute (ordSQL), dbFailOnError
        Exit Sub
    errhandler:
        If Err.Number = 3022 Then
            updSQL = "UPDATE tblOrderDetail SET Quantity = " & Me.Qty.Value & _
                ", WasChanged = True WHERE OrderID = " & Me.txtOrd.Value & " AND ProductID = '" & Me.ProductID.Value & "';"
                Debug.Print updSQL
            CurrentDb.Execute (updSQL)
        End If
    Cancel = True
    End Sub


    Thanks for your help!!

    Wednesday, August 23, 2017 10:17 PM
  • Thanks. Two things: 1. Try putting Cancel = True at the very beginning, and 2. Add Me.Qty.Undo right after it Hope it helps... By the way, you are only changing the Qty field and not any other fields, right?
    Wednesday, August 23, 2017 11:37 PM
  • That is correct.

    Will try and let you know - thanks for being so diligent!


    Thanks for your help!!

    Wednesday, August 23, 2017 11:38 PM
  • OK, so that works, but...

    It updates the table as you'd expect - but on the form the quantity is wiped out - appears to have no value.


    Thanks for your help!!

    Wednesday, August 23, 2017 11:44 PM
  • Hi plynton,

    do you mean only quantity value is removed and all other value you can able to see?

    what about saving the value in variable temporary before updating the record and again assign the value to control after updating the record.

    you can try to do it and let us know about the result.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 24, 2017 1:14 AM
    Moderator
  • OK, so that works, but...

    It updates the table as you'd expect - but on the form the quantity is wiped out - appears to have no value.


    Thanks for your help!!


    Hi, I expected that too but didn't want to jump the gun. Try issuing a requery at the end of the code. However, it will move the record pointer to the first record, so you'll have to navigate back to the same record you just updated using the Bookmark method.
    Thursday, August 24, 2017 1:23 AM
  • Both solutions (Deepak / DBguy) result in a message: (2115) The macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing XXXXXXXXXXX from saving data in the field.

    Thanks for your help!!


    • Edited by plynton Thursday, August 24, 2017 11:13 AM
    Thursday, August 24, 2017 10:37 AM
  • Hi plynton,

    can you show us your updated code.

    so that we can see how you are working and how we can solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 25, 2017 8:37 AM
    Moderator
  • Hi Deepak,

    I'm using the code above.  However, I've added a step in the Exit event to set Response = acDataErrContinue.  Keep in mind that the challenge stems from the fact that I use an LEFT OUTER join from Product to Orders - giving me a list of all products (records don't necessarily exist in Order Details).  If there is a better way to do this - I'm all ears.

    Thanks again - really appreciate the help!


    Thanks for your help!!

    Friday, August 25, 2017 5:12 PM
  • Hi,

    I asked a question earlier and looking back at your code, it seems you're updating more than one field (Qty) in the record. Is this correct? If so, why did you choose to use the Qty field to save the record changes to the table? Is Qty the last control on the form?

    If you're allowing the user to make changes to more than one field in the query/form to save to the table, then I think a better event to use is the Form's BeforeUpdate event rather than the control's event.

    Hope it helps...

    Friday, August 25, 2017 5:26 PM
  • Hi DBguy - I only update one field in the form - Qty.  However, when updating the DB, if my insert fails (unique index violation), I update the Quantity field and also a 'WasChanged' field - setting it to true.

    Sorry - didn't mean to mislead anyone.

    Thanks!


    Thanks for your help!!

    Friday, August 25, 2017 5:39 PM
  • It's fine. Still, it may be worth it to try moving the code to the Form level rather than just the field/control event.

    Please give it a shot and let us know how it goes.

    PS. Please remember to change Me.Qty.Undo to simply Me.Undo, if you use the form's event.

    Cheers!

    Friday, August 25, 2017 5:45 PM
  • The problem with this is that the Me.Undo wipes out the value that I've placed in my Qty text box.  Then when it tries to insert, there is no value for Quantity.

    Here is my updated code in the Forms Before Update...

    Cancel = True
    Me.Undo
    On Error GoTo errhandler
            ordSQL = "INSERT INTO tblOrderDetail (OrderID, ProductID, ProductName, Quantity, RetailerCost) VALUES (" & _
                Me.txtOrd.Value & ",'" & ProductID.Value & "','" & ProductName.Value & "'," & Me.Qty.Value & _
                "," & Me.RetailerCost.Value / Me.CasePack.Value & ");"
            CurrentDb.Execute (ordSQL), dbFailOnError
            Debug.Print ordSQL
        Exit Sub
    errhandler:
        If Err.Number = 3022 Then
            updSQL = "UPDATE tblOrderDetail SET Quantity = " & Me.Qty.Value & _
                ", WasChanged = True WHERE OrderID = " & Me.txtOrd.Value & " AND ProductID = '" & Me.ProductID.Value & "';"
                Debug.Print updSQL
            CurrentDb.Execute (updSQL)
        End If
        Call upd_form_hdr

    End Sub


    Thanks for your help!!

    Saturday, August 26, 2017 11:24 AM
  • Hi, In that case, try storing the value of Qty into a variable first before using Me.Undo and then use the variable in your SQL statement. Hope it helps...
    PS. Don't forget to store the other form values as well. Or, try moving Me.Undo just above the CurrentDb.Execute line. Sent from phone...
    • Edited by .theDBguy Saturday, August 26, 2017 3:52 PM
    Saturday, August 26, 2017 3:49 PM
  • So close - almost there!

    The last remaining issue is that the UNDO wipes out the qty value on the form.  Any way to show the value?  I know I could do a requery, but then I'd go back to record one each time, correct?  Not necessarily the user experience I'm looking for.


    Thanks for your help!!

    Saturday, August 26, 2017 4:06 PM
  • Hi,

    Glad to hear you're making good progress. Here are the steps I think we need to take:

    1. Store the values for the UPDATE query into memory (variable)

    2. Store the ID of the record into memory (bookmark)

    3. Cancel the event and Undo the form changes

    4. Execute the UPDATE query

    5. Requery the form

    6. Navigate the form to the previous record using the ID (bookmark)

    Hope it helps...

    • Marked as answer by plynton Sunday, August 27, 2017 1:11 PM
    Saturday, August 26, 2017 4:14 PM