Access table: subtracting two fields in a row and the result should update the field value of the next row

Question

In the below data, I want to update the Available Qty for the next part ID by showing ( AvailQty - ReqQty) from the previous row. In this instance, the Avail Qty for the second row should show as 170 and the third row as 169. The fourth row should show 171 as it is a different part ID than the first three rows.

PartID	Pty	ReqQty	AvailQty
1001	6	1	171
1001	7	1	171
1001	15	1	171
1002	16	1	171
1002	28	2	171
1003	52	2	171
1004	66	3	171
1004	68	4	171

My code is as below. 

Private Sub UpdateAvailQty()

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Set db = CurrentDb()
Dim sqlStr As String
Dim prevPartID, nextPartID As Long
Dim prevAvailQty, nextAvailQty, prevReqQty, i As Double


sqlStr = "Select PartID, Pty, ReqQty, AvailQty from Temp1"

Set rs1 = db.OpenRecordset(sqlStr)
Set rs2 = db.OpenRecordset(sqlStr)

rs1.MoveFirst  'refers to the first row in the table
rs2.MoveFirst   'Currently refers to the first row of the table

prevPartID = rs1.Fields(0)
nextPartID = rs2.Fields(0)
preReqQty = rs1.Fields(2)
preAvailQty = rs1.Fields(3)
nextAvailQty = rs2.Fields(3)


Do While Not rs1.EOF

i = prevAvailQty - prevReqQty

Do While Not rs2.EOF

rs2.MoveNext 'refers to the second row of the table.

If (prevPartID = nextPartID) Then

rs2.Edit

nextAvailQty = i

rs2.Update

Else

rs1.MoveNext
rs2.MoveNext


End If

Loop

rs1.MoveNext

Loop

rs1.Close
rs2.Close

Set rs1 = Nothing
Set rs2 = Nothing

End Sub

I'm getting a run time error '3021' No current record at rs2.Edit

Can you please suggest what am I doing wrong? Can you also please confirm if my code looks ok.

Thank you.

Answer 1

I found several errors in your code; should definitely add an Option Explicit statement:

preReqQty and preAvailQty are never declared, should be prevReqQty and prevAvailQty.

Next, between rs2.edit and rs2.update you never make any change to the record set.

Finally, you're looping too much, getting to the end of file, thus getting Error 3021. No current record, because the record set is empty.

Try this:

Private Sub UpdateAvailQty()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SELECT PartID, Pty, ReqQty, AvailQty FROM Temp1")
    Set rs2 = db.OpenRecordset("SELECT PartID, Pty, ReqQty, AvailQty FROM Temp1")
    
        rs1.MoveFirst   'Ensure we're starting with the first record in the table

        rs2.MoveFirst   'For rs2, we want the next record, so we'll start with the first
        rs2.MoveNext    'then move to the next, in order to compare first record with second record
        
        
        Do While Not rs2.EOF
        
            If rs1!PartID = rs2!PartID Then
                'Update the quantity if the PartID is the same
                rs2.Edit
                    rs2!AvailQty = rs1!AvailQty - rs2!ReqQty
                rs2.Update
            
                rs1.MoveNext
                rs2.MoveNext
                
            Else
                'If PartID is not the same, move to next record
                rs1.MoveNext
                rs2.MoveNext
                
            End If

        Loop
            
    rs1.Close
    rs2.Close
    db.Close
        
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
        

End Sub