Error 3420 on rs transation on 2nd run

[fxchase]

Goofy problem I'm seeing and haven't found what is causing it so tossing it out to the masses to see if anyone has encountered this.

Running a transaction in MS Access 2000 code. First pass works perfectly...2nd pass results in 3420 error (Object Invalid or no longer set).

The culprit for the error is a reference to rs.recordcount. Short of posting all of the other functions it should be noted that rs.close is never done in the procedures within the transaction.

The code:

Set ws = DBEngine.Workspaces(0)
ws.BeginTrans
Call UpdateInvFromLedger(Me.StockKey)

Function UpdateInvFromLedger(StockKey)

sql = "SELECT Inventory.* FROM Inventory WHERE Inventory.StockKey='" & StockKey & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(sql)

If rs.RecordCount > 0 Then 'this generates 3420 on 2nd pass

With rs
.MoveLast
.MoveFirst
For AvgCost = 1 To .RecordCount
stResp = getAvgCost(.fields("StockKey"), "Receiving")

If stResp <> .fields("StockKey") Then
.Edit
.fields("AvgCost") = stResp
.Update
End If

.MoveNext
Next

.MoveFirst
For QTYAvailable = 1 To .RecordCount
stResp = getFieldSum("InventoryLedger", "QTY", "StockKey", .fields("StockKey"))

If stResp <> .fields("QTYAvailable") Then
.Edit
.fields("QTYAvailable") = stResp
.Update
End If

.MoveNext
Next

.MoveFirst
For QTYBackordered = 1 To .RecordCount
stResp = getFieldSum("SODetail", "QTYBackOrder", "StockKey", .fields("StockKey"))

If stResp <> .fields("QTYBackOrdered") Then
.Edit
.fields("QTYBackOrdered") = stResp
.Update
End If

.MoveNext
Next

.MoveFirst
For QTYCommitted = 1 To .RecordCount
stResp = getQTYCommitted(.fields("StockKey"))

If stResp <> .fields("QTYCommitted") Then
.Edit
.fields("QTYCommitted") = stResp
.Update
End If
.MoveNext
Next

.MoveFirst
For QTYInUse = 1 To .RecordCount
stResp = getFieldSumWithCriteria("InventoryLedger", "QTY", "StockKey", .fields("StockKey"), "InvTrxType", "INUSE")

If stResp <> .fields("QTYInUse") Then
.Edit
.fields("QTYInUse") = stResp
.Update
End If
.MoveNext
Next

.MoveFirst
For QTYOnHand = 1 To .RecordCount
Call calcInvQTYOnHand(.fields("StockKey"))
.MoveNext
Next

End With
End If

End Function

 

[fxchase]

Disregard...I see the problem.

I'm resetting rs in the function by using the same variable so rs is no longer the original sql statement...must be late.