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

Posted on 2011-10-05
Last Modified: 2013-11-27
I have a a rather compilcated form with a number of child forms. Changes to the parent record can change associated records in child tables and all changes or additions to child records change values in the parent record.

Not always, but frequently, after making changes to a child record and upon attempting to make changes to the parent record, I get the following error message:

The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.

I know that this relates to unsaved records and I have added me.dirty = false to a number of locations in my VB code including the lost and got focus events for the main form and subforms. However, it hasn't corrected the problem.

Any ideas?  
Question by:dwcummings
    LVL 1

    Expert Comment


    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!

    Open in new window

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


    LVL 3

    Author Comment

    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.
    LVL 1

    Expert Comment


    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.

    LVL 3

    Author Comment

    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]
        End With
        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]
        End With
        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]
        End With
        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]
        End With
        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 Function
        MsgBox Error
        Resume Exit_Nice
    End Function

    Open in new window

    LVL 1

    Accepted Solution

    Here is the problem (I think):

    The SQL is updating the TABLE dbo_tblCurrentReturns which I assume is the RecodsSource for the parert form -- Once you run the SQL (in fact it is an update query), the form becomes dirty with pending update - that by itself is not  a problem UNTIL you try to update the form again - you get a write conflict because you create a second pending update that conflicts with the first one - this is the same as two users trying to update the same field concurrently (hence the write conflict message)

    My suggestion is to try the following:

    1. Requery the parent form IMMIDIETLY aftre the EACH TIME the SQL runs by adding a line right after you call the function or include it withing the function itself just before you exit the function.

    (ONLY) if the above does not solve the problem try to replace the SQL with ditect assingnments  to parent form as bellow:

    2. I assume that the parent form is frmProcessAReturn and its field names are the same as the table's field names (if not replace my syntax as appropriate)

    '[Your syntax up to here]
        'Comment out SQL		    
        '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
        'Upadte Parent Form by direct assignment
        Forms("frmProcessAReturn")("Gold") = VGold	
        Forms("frmProcessAReturn")("Silver") = VSilver    
        Forms("frmProcessAReturn")("Platinum") = VPlantinum    
        Forms("frmProcessAReturn")("Palladium") = VPalladim    
        Forms("frmProcessAReturn")("Gross") = VGross    
        Forms("frmProcessAReturn")("Processing") = VProcessing
        '..... etc.....
        Forms("frmProcessAReturn")("SayAmount") = VSayAmount
        'Requery the form
        Exit Function
        MsgBox Error
        Resume Exit_Nice
    End Function

    Open in new window

    Try one of those an let me know if that resolved the poblem
    LVL 3

    Author Closing Comment

    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.
    LVL 1

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 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

    9 Experts available now in Live!

    Get 1:1 Help Now