Solved

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

Posted on 2011-09-07
1
457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 58

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

724 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