Solved

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

Posted on 2011-09-07
1
453 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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

805 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