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

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?  
LVL 3
dwcummingsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jyk_ausCommented:

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).

Regards
Jacob


0
dwcummingsAuthor Commented:
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.
0
jyk_ausCommented:

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.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dwcummingsAuthor Commented:
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

Open in new window

0
jyk_ausCommented:
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
    Forms("frmProcessAReturn").Requery

  
Exit_Nice:
    Exit Function
    
Calculate_Error:
    MsgBox Error
    Resume Exit_Nice
    
    
End Function

Open in new window


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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwcummingsAuthor Commented:
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.
0
jyk_ausCommented:
You are most welcome.
Sorry for the late reply I was away (in hospital) and did not have my computer with me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.