Loop through Recordsetclone set focus to each record

[Dangas70]

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

[arnelgp]

you can make the process simple by just creating a Total Query and does
not require you to maintain any Inventory table.

select Item, Sum(Switch([LineStatus]="Received", [InOutQty], [LineStatus]="Returned", -[InOutQty], True, [InOutQty])) As Quantity_On_Hand
From yourTable
Group By Item

[Dangas70]

Thanks arnelgp for your input, I had thought about a query but wasn't quite sure how to go about it. I am a little confused though, when you say it doesn't require maintaining inventory table, but then at end of query I am assuming this goes to Quantity_on_Hand in table, can you explain that a little better, sorry for my ignorance I am on a crash course here but learning more each day.

[Dangas70]

I will play around with the query method as another option but with some trial and error I have managed to get VBA Code working, now for my next project I need to disable button once submitted. Code so far


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset2
Set db = CurrentDb
Set rs = Me.RecordsetClone
'On Error GoTo edit_error






rs.MoveLast
rs.MoveFirst


Dim idx As Integer
For idx = 1 To rs.RecordCount


Forms!frmReceiveInventory![frmReceiveInventoryDetails].SetFocus
DoCmd.GoToRecord , , acGoTo, idx


Set rst = db.OpenRecordset("select Quantity_On_Hand from Inventory where Item= '" & Me.Item & "';")


[Item].SetFocus


MsgBox "setrs" & idx
MsgBox "InOutQty" & Me.InOutQty


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

[Dangas70]

Next idx was cut off the bottom of that,

[arnelgp]

Thanks arnelgp for your input, I had thought about a query but wasn't quite sure how to go about it. I am a little confused though, when you say it doesn't require maintaining inventory table, but then at end of query I am assuming this goes to Quantity_on_Hand in table, can you explain that a little better, sorry for my ignorance I am on a crash course here but learning more each day.

it doesn't go (update the any table) to Inventory table.
Quantity_On_Hand on the query is a Calculated Column.

see attached. see Table1, then open Query1.
view Query1 in design view.

[Dangas70]

Thanks, I thought that may be the case as I tried a query where it would send it to the table but found it wanted a calculated column, I didn't know if there was a workaround for that. Problem is I need to store the value in the inventory table for stock reports, calculating low stock etc.

[OBP]

Do you still need help with this?

[RPFeynman06]

One rule of thumb is never store a calculated value as it data changes in a database. What you you require is a reporting technique to generate a report of what stock is currently on hand. I can help if you want that.