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

I'm using VB6 and access97,
I have an ADODC on one of my forms,
when I try to update with :
Adodc1.recordset.Update I get the following error:

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

my form contains a comboBox  with user names, the adodc presents each user's records.
(by clicking the combo and choosing a different name I'm refreshing the adodc with a new query)

The interesting thing is:
THE ERROR OCCURS ONLY when trying to update the first usre's records,
for other users it works fine.

This is a microsoft bug, I read this article:
(microsof solution:
RESOLUTION: To resolve this problem, remove the default value that is specified for the numeric field in the Access database table.)

and did what was suggested, but it still doesnt work.
any ideas?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I believe that this may relate to a problem with the primary index field.  When updating the record are you absolutely sure that the primary index field is valid.
zolpoAuthor Commented:
I dont have a promary key in that table,
That is why you are getting the error. Set one of the fields as a primary (if necessary add an autonumber field) and the problem should be resolved.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

zolpoAuthor Commented:
I will try that solution ,
But  how come I get the error only for the first set of records?
I found the same problem in one of my projects, it seemed to be intermittent and varied according to which service pack I had installed. Setting a primary field solved it for me.
zolpoAuthor Commented:
I defined a primary key, now the error occurs only when changing one  of the fields,
(text box that holds the hour)
The error happens when trying to change that certain field, or when trying to change any field after
the error first time occured.
If you have set an auto number as the primary field then you need to check the hour field before it is updated. If necessary set a default value for this field before it is edited. It sounds like you may be passing an invalid value (possibly a null).

If the primary field is not an auto number then I suggest that you check both the value of the hour field and the index field.
zolpoAuthor Commented:
I have set the primary key as auto number,
what do you mean by: "check the hour field before it is updated" ?
what do I need to check? and how do I check it?

You need to check that the value you are passing from hour text box is appropriate for the Access data field. This can be accomplished in a number of ways depending upon the way your program has been set up. Things to check:

1) Is the access field a date/time field or a long/Integer?
2) Interrupt your program on the Adodc1.WillChangeRecord event and examine the value passed to the hour field before it is updated. In fact it would probably be wise to check the value of all fields before the update takes place.
zolpoAuthor Commented:
I dont understand what the problem is, because I'm printing the hour value in Adodc1.willChangeRecord, and it seems alright, more then that, everything is working fine for all users I have (the user's records) except the first user's record.
any ideas?
thanks a lot!
Are you using the latest service pack (VB6/SP5). There were a few problems with ADO originally but most of them got sorted out by SP5.
zolpoAuthor Commented:
Yes, I am using sp5,
Only other things that come to mind are:

1) Is the code short enough to post so that we can look at the problem directly.

2) Would upgrading the data to Access 2000 be a viable proposition?
zolpoAuthor Commented:
Private Sub txtEnter_GotFocus()
    oldVal = txtEnter.Text
End Sub
on lost focus i check if the value entered to check box is legal, if not- remaining the last value
Private Sub txtEnter_LostFocus()
    If Not IsDate(txtEnter.Text) Then
        'MsgBox ("ערך לא חוקי")
        Debug.Print "not legal"
        txtEnter.Text = Format(oldVal, "short time")
        Call setHours
    End If
End Sub
calculating time difference exit-enter and returning in a
string format
'setting (exit - enter) in lblHours and lblSumHours
Private Sub setHours()

Dim diff As Double
Dimm totShifHrs as Double
If IsDate(txtEnter.Text) And IsDate(txtExit.Text) Then
    diff = TimeMinuteDiff(txtEnter, txtExit)
    'סהכ שעות במשמרת
    totShifHrs = Format(diff / 60, "Standard")
    lblHours.Caption = hoursFromMinutes(diff)
    lblSumHours.Caption = totShifHrs
End If
End Sub
'returning the hours as a string hh:mm
Public Function hoursFromMinutes(total_min As Double) As String
    Dim hours As Double
    Dim mafrid As String
    Dim h As Integer
    Dim minutes As Integer
    mafrid = ":"
    hours = total_min \ 60
    minutes = total_min - (hours * 60)
    If minutes < 10 Then
        mafrid = mafrid & "0"
    End If
    hoursFromMinutes = CStr(hours) & mafrid & CStr(minutes)
End Function
'returning startTime - endTime as minutes
Public Function TimeMinuteDiff(Stime, Etime) As Long
Dim StartTime As Date, EndTime As Date

StartTime = TimeValue(Stime)
EndTime = TimeValue(Etime)
If EndTime < StartTime Then
    EndTime = DateAdd("d", 1, EndTime)
End If
TimeMinuteDiff = DateDiff("n", StartTime, EndTime)
End Function

setHours, hoursFromMinutes and TimeMinuteDiff are Just to return the time difference as a string.

Private Sub btn_Update_Click()
On Error GoTo errUpdate
     Adodc1.Recordset.Update *************** problem here
     Exit Sub

    Debug.Print Err.Number
    Debug.Print Err.Description
End Sub


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

I dont think that other parts of the code are relevant couse the problem is only when
changing txtEnter

A suggestion.
Just before the line

Adodc1.Recordset.Update *************** problem here

insert a messagebox to show the values of all of the fields in the relevant table (before the update happens). Do all the field values look OK?.
zolpoAuthor Commented:
Hi Tom,
I tried what u suggested but it all seemed to be allright,
I decided to delete the first record from the db,
and now it work for athor users.
Thanks A LOT for all your help!
Thank you Zolpo - glad you got it sorted.
I had a similar problem and aloo found the article ID 300586 'row cannot be Located for updating" with the solution of removing the default value for the specified numeric fiield in the access database.
This did not work for me either. However, this solution did:
In the code where the update occurs (could also be a movefirst or a movelast), before updating, check that each numeric value has been assingned a value. I used 0 if blank. This worked for me.

    ' fix for 'row cannot be located for updating'. The default 0 does not work.
    ' See Article ID 300586 bug error 'Row cannot be located for updating'
    Dim i As Integer
    For i = 0 To 27
        If Len(Trim(txtSlot(i).Text)) = 0 Then
            txtSlot(i).Text = "0"
        End If

I am guessing that the bug is that the default numeric value set in the database design does not work, so you need to enter the data manually where needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.