Loop through recordset and update

[ghloid]

Hello all,

I have the following code which SHOULD create a recordset (mev3 is the data for it) and update a field in the set while it loops through. Apparently, this does not seem to work though. Here is the code:

Dim VarField As String
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim Cont As String
Dim IDNo As Integer
Dim rst As ADODB.Recordset
Dim mev3 As String
Dim ReqID As Integer


mev3 = "SELECT PointLkp_Tbl.sys_Requirement_ID, PointLkp_Tbl.IssueNo, PointLkp_Tbl.ID FROM PointLkp_Tbl ORDER BY PointLkp_Tbl.sys_Requirement_ID, PointLkp_Tbl.IssueNo;"

'establish a for loop here to look for a sys_requirement_id field value and update based off that reqid value
For ReqID = 8 To 14
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open mev3, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
IDNo = 1
With rst
rst.MoveFirst
Do While Not rst.EOF
'do checking here to see if the current sys_requirement_id field = the reqid field...if it does, update the ID field to the IDNo value
If (rst.Fields("sys_Requirement_ID").Value) = ReqID Then
rst.Fields("ID").Value = IDNo
'increase the IDNo variable for next run through
IDNo = IDNo + 1
End If
rst.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Next ReqID

The PointLkp_Tbl table that the mev3 query statement refers to has only 4 fields: ID, sys_requirement_id, PointLbl, and IssueNo. We're not talking about a large set of data here.

What it SEEMS to be doing is looping through correctly at first, then when it gets to a sys_requirement_id that IS in the for loop (say 9 for instance), it trys to update ALL the records that have sys_requirement_id = 9 at once. I thought it would update only the current record it was on, then move to the next record with sys_requirement_id = 9 and update that record's ID field (to 2 if the loop worked correctly). It's not doing that though, as I keep getting the following error:

Key column information is insufficient or incorrect. Too many rows were affected by update

I kind of stole this code from another project I was working on, where it DID step through the records as expected (first record got updated, then second one, then third, and so on till the end of the recordset).

Perhaps something is wrong where I'm defining my recordset. Since this code was taken from another project, I really only copied and pasted and modified some variables. I'm not 100% sure I know what it's doing as this is the first time I've really worked with defining and modifying a recordset on the fly through code.

I hope that explains it well enough. I'm working with Access 2000 here (not that I think that's very important, but I thought I'd share).

If anyone has any ideas, I'd love to hear em.

Thanks!!

 

[FancyPrairie]

Don't have time to understand your problem (gotta go)...but you might try forcing the update after each iteration.

rst.Fields("ID").Value = IDNo
rst.update

 

[Lightning]

       Do While Not rst.EOF
        'do checking here to see if the current sys_requirement_id field = the reqid field...if it does, update the ID field to the IDNo value
        If (rst.Fields("sys_Requirement_ID").Value) = ReqID Then
            [COLOR=blue]rst.Edit[/color]
            rst.Fields("ID").Value = IDNo
            [COLOR=blue]rst.Update[/color]
        'increase the IDNo variable for next run through
            IDNo = IDNo + 1
        End If
        rst.MoveNext
        Loop

HTH
Lightning

 

[ghloid]

Thanks for the advice guys! Both good suggestions, but it looks like it's 'stoopidity' on my part that broke it.

It turns out that the main problem seems to be that the PointLkp_Tbl table didn't have a primary key in it. For some reason that's needed for the recordset loop iteration to work (I suppose so it can differentiate between all the records that may have the same sys_requirement_id). When I look at the MDB where I copied the code from originally, it had a primary key in it's table source too.

Seems to also make sense since the error I was getting was talking about key column data being insufficient (a hint of an idea spread from that). Key column data = primary key perhaps? Hmmmmm...

Anyway, sorry if some of this doesn't make much sense, but it's probably because I've been coding too long, and my mind is mush. Also, probably has something to do with the fact that I'm new to the use of recordsets.

Thanks again for the help though. I'll see how it works out from here.