Link to home
Start Free TrialLog in
Avatar of WSIT
WSIT

asked on

"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

ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial