table showing all stock qty changes from old to new value

[rainbows]

the form below shows me the 2 item i have purchased
the middle sub form tell us what is received and in that form i tick the box " stock change "
the form below that then changes the "on order qty" and the stock qty this is done by a query

i have some vba that when i manually change the stock qty it
goes to a table with all the changes that have been made see below

i was hoping this would work after i ticked the " stock change" button as the quantity has changed but it dont , and ideas on how to do that

i also have 4 forms that are related to the stocklist table of which any of them 4 can change the stock qty. so is there a way that i ont have to put this code on all 4 forms attached to the stock qty field

thanks steve

Private Sub StockQty_AfterUpdate()

Dim OldValue As String
Dim NewValue As String
Dim MaterialID As Double
MaterialID = Me.MaterialID

OldValue = Me.StockQTY.OldValue
NewValue = Me.StockQTY

DoCmd.RunSQL "INSERT INTO TblDataChanges ( MaterialID, ChangeDate, ControlName, OldValue, NewValue ) VALUES ('" & MaterialID & "',Now(),'stockqty' ,'" & OldValue & "', '" & NewValue & "');"
Exit Sub
End Sub

 

[pbaldy]

To answer both questions, I'd create a public function that took the 3 values as input arguments and executed the SQL. Then call that function from the desired places, grabbing the values as appropriate to each.

 

[RogerCooper]

You are doing this backwards. You should have a table of all inventory transactions. When an inventory transaction is created, you should then update the other fields. If there is a problem, you always go back to the table of inventory transactions and determine the correct values.

 

[Pat Hartman]

i also have 4 forms that are related to the stocklist table of which any of them 4 can change the stock qty. so is there a way that i ont have to put this code on all 4 forms attached to the stock qty field

I agree with @RogerCooper , inventory is always easier to manage when you use the transaction model rather than direct updates to a summary record. Each inventory transaction logs an in or out action. Then summing the transactions gives you the current value. AND if you adjust inventory in multiple places, you can use the shared function.