?
Solved

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

Posted on 2011-09-07
1
Medium Priority
?
467 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:WSIT
1 Comment
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 36495217

 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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 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