Row cannot be located for updating. Some values may have been changed since it was last read

Posted on 2011-05-12
Last Modified: 2012-05-11
I am using the attatched cde to update a DB table but i get the folloowing error

Row cannot be located for updating. Some values may have been changed since it was last read

can anybody help?
Dim rs As New ADODB.Recordset
        Dim sqlstr As String

        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        sqlstr = "SELECT * FROM PensionCategories WHERE PensionCategoryRef = 0"
        rs.Open(sqlstr, sqlCnn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
        rs.Fields("PensionCategoryName").Value = CategoryName
        rs.Fields("BandEmployer").Value = BandErs
        rs.Fields("BandEmployee").Value = BandEes
        rs.Fields("ActualEmployer").Value = ActualErs
        rs.Fields("ActualEmployee").Value = ActualEes
        rs.Fields("EmployerRef").Value = EmployerRef

Open in new window

Question by:ict-torquilclark
    LVL 21

    Accepted Solution

    LVL 1

    Author Comment

    the only thing i can see from those is that it doesnt update the record because there is no promary key

    shouldnt rs.addnew() deal with that?

    i have used this method for years and have never had to add a primary key manutally before?
    LVL 6

    Expert Comment

    I usually use BatchOptimistic:

            oRs.Open(SQL, Connection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic)

    Then the update instead is


    The Dynamic flag instead of the Static may help as well

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    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…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now