?
Solved

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

Posted on 2011-10-05
7
Medium Priority
?
520 Views
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?  
0
Comment
Question by:dwcummings
  • 4
  • 3
7 Comments
 
LVL 1

Expert Comment

by:jyk_aus
ID: 36921404

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
 
LVL 3

Author Comment

by:dwcummings
ID: 36921908
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
 
LVL 1

Expert Comment

by:jyk_aus
ID: 36923406

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:dwcummings
ID: 36923839
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
 
LVL 1

Accepted Solution

by:
jyk_aus earned 2000 total points
ID: 36928371
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
 
LVL 3

Author Closing Comment

by:dwcummings
ID: 36976141
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
 
LVL 1

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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