How to update a recordset

[RamanS]

I have this procedure, which opens two recordssets r1 and r2. But there seems to be a problem with the function because the loop does not end and it freezes my ms access application.

can someone please help me.


Private Sub cmdUpdateInventory_Click()

Dim db1 As Database
Dim db2 As Database

Dim rs1 As Recordset
Dim rs2 As Recordset

Set db1 = CurrentDb
Set db2 = CurrentDb

Set rs1 = db1.OpenRecordset("Products")
Set rs2 = db2.OpenRecordset("Select * from tblShipmentdetails where ShipmentID = 3")

rs1.MoveFirst
rs2.MoveFirst

Do While Not rs1.EOF
Do While Not rs2.EOF

rs1.Edit
rs2.Edit


rs1!QuantityOnHand = rs2!QuantityRecieved + rs1!QuantityOnHand

rs1.Update
rs2.Update
rs1.MoveNext
rs2.MoveNext

Loop

Loop

rs1.Close
rs2.Close

End Sub

[FishVal]

Hi, RamanS

No wonder it never ends if the count of records in rs1 more than the count of recirds in rs2. As soon as rs2 will reach EOF the internal cycle will never be entered and the external cycle will never end because rs1 cursor is being moved in the internal cycle.
On the other hand, if rs1.RecordCount<rs2.RecordCount, then the code will fail.
Anyway I can hardly imagine what the code is supposed to do.

Regards,
Fish

[Megalog]

It looks like it's simply trying to update inventory (adding received shipment quantity to what's already in stock).

RamanS, Can you post the table relationships? (table names & relevant fields) There's a much easier way to run this in a single loop...