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!!
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!!