Greetings all,
I seem not to be able to loop through a recordset with the code. When I run the code below it works on the first row but then does not work any further with a error message of 'Object invlaid or no longer set' . I have tried a .update but this does not work.
I have used similar code for single records but cannot work out how to get a recordset with multiple records to work.
The code I am using is;
Dim db As DAO.Database
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Set db = DBEngine(0)(0)
DoCmd.OpenQuery "sqry_Allocation_Edit" ' make table query
Set rs2 = db.OpenRecordset("ztbl_AllocationEdit") 'use that table
Do Until rs2.EOF
rs2.Edit
Set rs1 = db.OpenRecordset("tbl_Admin_Inventory")
rs1.Index = "WineNumber"
rs1.Seek "=", rs2![WINENUMBER]
rs1.Edit
rs1![Allocated] = rs1![Allocated] - rs2![Amount]
rs1.Update
rs1.Connection
rs2.Delete
rs2.Close
Loop
I thank you in advance.
rbinder
I seem not to be able to loop through a recordset with the code. When I run the code below it works on the first row but then does not work any further with a error message of 'Object invlaid or no longer set' . I have tried a .update but this does not work.
I have used similar code for single records but cannot work out how to get a recordset with multiple records to work.
The code I am using is;
Dim db As DAO.Database
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Set db = DBEngine(0)(0)
DoCmd.OpenQuery "sqry_Allocation_Edit" ' make table query
Set rs2 = db.OpenRecordset("ztbl_AllocationEdit") 'use that table
Do Until rs2.EOF
rs2.Edit
Set rs1 = db.OpenRecordset("tbl_Admin_Inventory")
rs1.Index = "WineNumber"
rs1.Seek "=", rs2![WINENUMBER]
rs1.Edit
rs1![Allocated] = rs1![Allocated] - rs2![Amount]
rs1.Update
rs1.Connection
rs2.Delete
rs2.Close
Loop
I thank you in advance.
rbinder
