MS Access 2003 - Another user edited this record and saved the changes before you attempted to save your changes

I assume that the parent form is open when you make changes to the child form, please confirm.

A write conflict usually oocurs when you change a value in a form before or during an event that also changes the same  or other values. For example, your Form.Current event is triggered after you updated the child form or and it (Form.Current)  is setting values to a field or fields.

When you make a change to a child form how does the child form update the parent?  Do you update the parent form using a recordset or do you use a diret assignment such as Parent![fieldname]=someting ?

Can you pinpoint the circumstance under which the error is triggered (can you replicate the error at wiil)?

If not, it might be an idea to check for Me.Dirty in various places around your code, someting like:

 

Debug.Assert Not Me.Dirty   ' form should not be dirty at this point!

Select allOpen in new window

This will stop your code running if the assertions fail (ie. when  Me.Dirty=True).

Regards
Jacob

Thanks for responding.

The child form is a subform within the parent form. The child form contains metal elements that need to be valued. Upon valuation, certain fields on the parent form need to be updated including metal element totals and the customer payment. There are 3 child forms that can affect the customer payment. Each child subform contains its own calculation routines and the parent form is updated via a combination of VB code and an SQL statement contained within a module. The former for gathering information and the latter for updating the parent fields.

In some cases, changes to the parent cause the recalculation of each of the child records, i.e., a change in the pricing date which affects the payment rate, which results in a recalculation of all the child records, which in turn causes changes to the parent record. This, however, does not usually cause the error unless the action is repeated.

The error almost always occurs after making a change to the parent record, followed by a change to one of the child records, followed by some other change to the parent record. An example would be adding a shipping charge to the parent record, followed by changing a variable in a child record (which triggers a recalc), followed by checking a box in the parent record to signify payment release.

I believe that the error occurs after my VB code updates the parent record, but the parent record is not saved. Or, the parent record is saved, but the form is not updated and doesn't know about the changes.

I hope that this makes the problem more clear.

parent form is updated via a combination of VB code and an SQL statement contained within a module.


Can you please post an example of  VBcode  and SQL statement that update the parent? I suspect that you may be updating the undelining table, in which case you will get the write error when the parent get the focus.

Sometime a simple requery after the update of the parent (from a subForm) may resolev the problem, something like:

Forms("parent name").Requery

But bear in mind that Requery triggers the Form.Current evet which may cause other problems.

Here is the code that updates the parent record.

Function CalculateTheReturn()
On Error GoTo Calculate_Error

    Dim rst As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset
    Dim rst3 As New ADODB.Recordset
    Dim rst4 As New ADODB.Recordset
    Dim VGold As Double, VSilver As Double, VPlatinum As Double, VPalladium As Double, VGross As Double
    Dim VProcessing As Double, VNet As Double, VAmalgam As Double, VCost As Double
    Dim VBonus As Double, VAdvance As Double, VSwapItemAmount As Double, VSwapShipping As Double
    Dim VOtherFees As Double, VTotal As Double, VWireAmount As Double, VWireFee As Double, VCheckAmount As Double
    Dim MySQL As String, VSayAmount As String
       
    'Obtain the metals values
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * From [dbo_tblCurrentMetal] Where [Seq] = " & Forms!frmProcessAReturn!Seq & ";"
    With rst
        Do Until .EOF
            VAmalgam = VAmalgam + rst![ValueAM]
            VSilver = VSilver + rst![ValueAG]
            VGold = VGold + rst![ValueAU]
            VPalladium = VPalladium + rst![ValuePD]
            VPlatinum = VPlatinum + rst![ValuePT]
            VProcessing = VProcessing - rst![ValueProcessing]
            .MoveNext
            Loop
    End With
    rst.Close
    Set rst = Nothing
    
    'Obtain the swaps values
    rst2.ActiveConnection = CurrentProject.Connection
    rst2.Open "Select * From [dbo_tblCurrentSwap] Where [Seq] = " & Forms!frmProcessAReturn!Seq & ";"
    With rst2
        Do Until .EOF
            VSwapItemAmount = VSwapItemAmount + ![Value]
            .MoveNext
            Loop
    End With
    rst2.Close
    Set rst2 = Nothing
    
    'Obtain the other fees
    rst3.ActiveConnection = CurrentProject.Connection
    rst3.Open "Select * From [dbo_tblCurrentOtherFees] Where [Seq] = " & Forms!frmProcessAReturn!Seq & ";"
    With rst3
        Do Until .EOF
            VOtherFees = VOtherFees + ![Value]
            .MoveNext
            Loop
    End With
    rst3.Close
    Set rst3 = Nothing
    
    'Obtain the return values
    rst4.ActiveConnection = CurrentProject.Connection
    rst4.Open "Select * From [dbo_tblCurrentReturns] Where [Seq] = " & Forms!frmProcessAReturn!Seq & ";"
    With rst4
        Do Until .EOF
            VBonus = ![Bonus]
            VAdvance = VAdvance + ![Advance]
            VSwapShipping = VSwapShipping + ![SwapShipping]
            .MoveNext
            Loop
    End With
    rst4.Close
    Set rst4 = Nothing
    
    VGross = VGold + VSilver + VPlatinum + VPalladium
    VNet = VGross + VProcessing
    VCost = VNet + VAmalgam
    VTotal = VCost + VBonus - VAdvance - VSwapItemAmount - VSwapShipping - VOtherFees
    VCheckAmount = VTotal
    VSayAmount = TranslateAnAmount(VCheckAmount)
    
    MySQL = "Update dbo_tblCurrentReturns Set Gold = " & VGold & ", "
    MySQL = MySQL & "Silver = " & VSilver & ", "
    MySQL = MySQL & "Platinum = " & VPlatinum & ", "
    MySQL = MySQL & "Palladium = " & VPalladium & ", "
    MySQL = MySQL & "Gross = " & VGross & ", "
    MySQL = MySQL & "Processing = " & VProcessing & ", "
    MySQL = MySQL & "NetAmount = " & VNet & ", "
    MySQL = MySQL & "Amalgam = " & VAmalgam & ", "
    MySQL = MySQL & "Cost = " & VCost & ", "
    MySQL = MySQL & "Bonus = " & VBonus & ", "
    MySQL = MySQL & "Advance = " & VAdvance & ", "
    MySQL = MySQL & "SwapItemAmount = " & VSwapItemAmount & ", "
    MySQL = MySQL & "SwapShipping = " & VSwapShipping & ", "
    MySQL = MySQL & "OtherFees = " & VOtherFees & ", "
    MySQL = MySQL & "Total = " & VTotal & ", "
    MySQL = MySQL & "WireAmount = 0, "
    MySQL = MySQL & "WireFee = 0,"
    MySQL = MySQL & "CheckAmount = " & VCheckAmount & ", "
    MySQL = MySQL & "SayAmount = '" & VSayAmount & "' "
    MySQL = MySQL & "Where Seq = Forms!frmProcessAReturn!Seq;"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL MySQL
    DoCmd.SetWarnings True
    
Exit_Nice:
    Exit Function
    
Calculate_Error:
    MsgBox Error
    Resume Exit_Nice
    
    
End Function

Select allOpen in new window

Sorry it took me so long to respond to your suggestion.

I tried the first suggestion and it worked, but it seemed to slow down the processing time.

I then tried your second suggestion, the direct assignment, and it works like a charm.

Again, thanks for the help.

You are most welcome.
Sorry for the late reply I was away (in hospital) and did not have my computer with me.