Stock Control

[Hello1]

From these tables and relationships, I would like to make next. Whenever I complete a Purchase Order, I would like the database to automatically write a record in the stock movements table, with the products used in the PO, quantity, the date and PO_ID, also the movement type should be Stock in.
Same for the invoice, just the movement type would be Stock out.
I saw some tutorials where the users store the stock level in the products table but many suggested me to not do that.

Thanks

[orange]

See this material from Allen Browne for ideas.

See if this data model helps.

[Hello1]

I dont have any experience with VBA at all, makes it more difficult for me.
From the northwind template, I understood how they calculate the left quantity, when to reorder and the rest but I dont understand next.
There is an Inventory Transactions table which contains ID, Type, date, Product ID, quantity, Purchase order ID and Customer order ID. Now, I understand the purpose of the fields, with them I calculate through query the left quantity, sold products, purchased products and so on.. but what I dont understand is when I finish a Purchase Order from a form, how is that record automatically written in the Inventory table as the type Purchased, or when I finish an Order, just the type will be sold.
Do I need to make the form Purchase Order from the table Inventory Transactions? So when I make the form I chose the table Inventory Transactions and take the fields Product_ID, quantity, date and the type? and then the rest add from other tables?

Thanks

[Hello1]

I tried to make a button which would execute the next code.

PrivateSub cmdOrder_Click()Dim strSQL AsStringr

    strSQL ="INSERT INTO StockMovement (ID_Product, Status, Quantity, ID_PurchaseOrder) VALUES ("& _
               Me.frmPurchaseOrderDetails_Subform.Form!comboboxProduct &", '"& _
               Me!txtStatus &"', "& _
               Me.frmPurchaseOrderDetails_Subform.Form!txtQuantity &", "& _
               Me!txtID_PurchaseOrder &");"
    DoCmd.RunSQL strSQL
    Me.Requery

EndSub

The code was supposed to copy records from my main form Purchase Orders and the sub-form Purchase Order Details which is in the main form and add them to the target table StockMovements. It works but it adds only the first record of the sub-form to the table. What I wanted was to copy all the records from the sub-form to the table and not just the first one. To be more clear, when I open the main form Purchase Orders, in its sub-form there will be all the products ordered with that Purchase order. Then I researched a bit and got a suggestion to try the next code

Dim strSQL AsString
strSQL =""

 ForEachItemInGroup
    strSQL = strSQL &"INSERT INTO table (field1,field2) VALUES (value1,value2)"Next

So I implemented it and got this

Private Sub cmdOrder_Click()
Dim strSQL As String 
strSQL = "" 
For Each Item In Group 
strSQL = strSQL & "INSERT INTO StockMovement (ID_Product, Status, Quantity, ID_PurchaseOrder) VALUES (" &
     Me.frmPurchaseOrderDetails_Subform.Form!comboboxProduct & ", '" & 
     Me!txtStatus & "', " & 
     Me.PurchaseOrderDetails_Subform.Form!txtQuantity & ", " & 
     Me!txtID_PurchaseOrder & ");" 
Next 
DoCmd.RunSQL strSQL 
Me.Requery 
End Sub

However, when I run it i get the runtime error "13 ", "Type mismatch". When I go to debug it makes the "For Each Item In Group" yellow.
I was thinking to add a check box in the sub-form and when its checked to run the first code, so I would have to check every item in the sub-form, but I dont know how to select the values from the main form in VBA, got a lot of errors

Last suggestion I got was

you need a select query or - much faster - use DAO to open the target table as a recordset and then, as source, loop the RecordsetClone of your subform and copy the records to the target table one by one

But I dont know how to implement that, if someone got an example of it, it would be awesome.

Thanks again n sorry for bothering

Edit: I managed to add the check box in the subform and make the code work for it However, I still would prefer one button doing all that because its not so good that I have to check every item in the list