I am new to this and requiring some help, I have setup an inventory management system and have a purchase order form with a continuous subform inside a tab for order lines which is working for the most part, when I receive stock I have a field InOutQty that I enter the amount received and then a combobox where I can select Received. I have VBA code which will run on event AfterUpdate of Received Status and add the stock to my Quantity on hand in the Inventory Table which works fine, one record at a time. My goal is to be able to set all received records to received and then press a command button which runs through all the records and updates inventory, then I want to disable that button so it can't be entered twice, I haven't tackled that bit yet until I get the loop working right. The code I have been playing with loops though a recordsetclone and runs my update to inventory I have checked that it loops but it just updates the same line which was last selected as many times as the recordcount, I think I need to maybe setfocus on each record as it loops through, but open to any suggestions, possibly throw out my code and start again with a different method. My attempt at code attached. I have a few message boxes to tell me what was going on and I can see the records count through but the InOutQty just keeps returning the value of last selected record.
Private Sub StockUpdate_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim rst As DAO.Recordset2 Set db = CurrentDb Set rs = Me.RecordsetClone rs.MoveLast rs.MoveFirst Dim idx As Integer For idx = 1 To rs.RecordCount Me.Item.SetFocus MsgBox "recordcount" Set rst = db.OpenRecordset("select Quantity_On_Hand from Inventory where Item= '" & Me.Item & "';") MsgBox "setrst" & idx MsgBox "InOutQty" & Me.InOutQty '" & val & "';" With rst .Edit If Me.LineStatus.Value = "Received" Then !Quantity_On_Hand = !Quantity_On_Hand + Nz(Me.InOutQty, 0) End If If Me.LineStatus.Value = "Returned" Then !Quantity_On_Hand = !Quantity_On_Hand - Nz(Me.InOutQty, 0) End If .Update End With Next idx End Sub