"This record has been changed by another user since you started editing ... " - Error in Access 2010

Hi

This error has been driving me nuts.  I have a form which I use for paying invoices.  The form has a button which on being clicked displays a drop down list of unpaid invoices.  The chosen invoices's details then appear as a record on the form.  I then click elsewhere on the screen I get the "This record has been changed by another user since you started editing it" error.  I can choose to drop changes/save changes etc but I would like to know why this error is coming up in only Access 2010 and not 2003.

I've gone through the code (attached) and have discovered that if i comment out the line that is marcked with ****** then this code works fine in 2010.  

(Payment Reference, Invoice, NetAmount, VATAmount, Created_By are controls in the form.  GetCountOfInvoices is a routine which sets wt1 as a recordset of the table Invoice_Headers)

I hope I have added enough info.

Thanks
Private Sub Add_Further_Invoices_Click()
Dim VATAmount As Currency, NetAmount As Currency
GetCountOfInvoices
Select Case NumberOfInvoicesLeft
    Case 1
        Forms!pf!Selected_Invoice = wt1!Code
        NetAmount = wt1!Net_Amount
        VATAmount = wt1!Vat_Amount
        If MsgBox("There is a single unpaid invoice for " & Format(NetAmount, "£#,###,###.00") & " (net) " & vbNewLine & "and " & Format(VATAmount, "£#,###,###.00") & " (VAT)" & vbNewLine & vbNewLine & "Is that the one you wish to be included in this payment?", vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub
    Case Else
        If Forms!pf!Selected_Supplier = "Non-Supplier Payment" Then
            GenericSelect "Select the invoice to be added", "SELECT * FROM Invoice_Pick_List WHERE Payment = 0", ""
        Else
            GenericSelect "Select the invoice to be added", "SELECT * FROM Invoice_Pick_List WHERE Supplier = " & Forms!pf!Selected_Supplier & " AND Payment = 0", ""
        End If
        If IsNull(Forms!pf!HouseKeepingKey) Then Exit Sub
        Forms!pf!Selected_Invoice = Forms!pf!HouseKeepingKey
        Set wt1 = finance.OpenRecordset("Select * from invoice_headers where code = " & Forms!pf!Selected_Invoice)
End Select
If IsNull(Invoice) Then
    Invoice = Forms!pf!Selected_Invoice
Else
    DoCmd.GoToRecord acDataForm, Name, acNewRec
    Invoice = Forms!pf!Selected_Invoice
End If
Net_Amount = wt1!Net_Amount
Vat_Amount = wt1!Vat_Amount
If IsNull(Payment_Reference) Then
    Payment_Reference = "Your reference is " & wt1!Invoice_Number
Else
    Payment_Reference = Payment_Reference & " and " & wt1!Invoice_Number
End If
Payment_Reference = Left(Payment_Reference, 199)
If IsNull(Bank_Account) Then Bank_Account = DLookup("Bank_Account", "Invoice_Headers", "Code = " & Invoice)
*********DoCmd.RunCommand acCmdSaveRecord ************
MaintainTransactionLog "Update Invoice Header", "Invoice_Headers", Invoice, "Update", 0, 0, 0, 0
Created_By = TransactionNumber
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Invoice_Headers SET Invoice_Headers.Updated_By = " & TransactionNumber & " , Invoice_Headers.Payment = " & Forms!pf!Selected_Payment & " WHERE Code = " & Invoice & " AND Deleted_By = 0"
DoCmd.SetWarnings True
NumberOfInvoicesLeft = NumberOfInvoicesLeft - 1
Select Case NumberOfInvoicesLeft
    Case 0: [Exit].SetFocus
                Add_Further_Invoices.Visible = 0
    Case 1: Add_Further_Invoices.Caption = "1 Further Invoice Available"
    Case Else: Add_Further_Invoices.Caption = NumberOfInvoicesLeft & " Further Invoices Available"
End Select
End Sub

Open in new window

WSITAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:

 Short answer; you were lucky to get away with it in Access 2003.

  These are what are refered to as timing errors.   That is, your performing a series of operations and on one pc and/or product, may work correctly.  On others, it fails because the speed of execution is different.

  For a quick workaround, try adding a:

  dbEngine.Idle dbrefreshcache

  Right after the save and see if that clears it up.  But that's really just a patch.  THe real fix is to look at what your doing and why.

Jim.
0
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.

All Courses

From novice to tech pro — start learning today.