You are saying that PurchasePrice and TotalPurchasePrice are not working in your Query. Where is the image of the Query showing these Calculated Fields?

Yeah so meanwhile I got a step further and got them working (a relation in the form query/recordsource was incorrect). But now my form is "locked". It doesn't let me add or change any data. In the form properties everything is set to yes (allow additions, allow deletions, etc.).

You have 2 joins for ProductID. This will have made the Recordset Not Updateable

Also see: Outer Joins

I watched them both and now got it like this (see image) but still it isn't updateble.

OrderDetailsT.ProductID --> ProductsT.ProductID = all records of OrderDetailsT and only those of ProductsT whose linked fields are identical
OrderDetailsT.ProductID --> PurchaseT.ProductID = all records of OrderDetailsT and only those of PurchaseT whose linked fields are identical
OrderDetailsT.SupplierID--> PurchaseT.SupplierID= all records of OrderDetailsT and only those of PurchaseT whose linked fields are identical

What am I missing?

I got it working. I removed the PurchaseT table from the form's recordsource. I added the PurchasePrice field with a statement in the query with this:

PurchasePrice: IIf(IsNull(OrderDetailsT.SupplierID);Null;DLookUp("PurchasePrice";"PurchaseT";"ProductID=" & OrderDetailsT.ProductID & " AND SupplierID=" & OrderDetailsT.SupplierID))

See the image below for the query.

I don't know if this is a correct way of doing things, so I would like to receive some feedback on it.

Other question is if it is also possible to do this WITH the PurchaseT table in the query?
I have a feeling that my tables are not properly setup? See image below.

The image below demonstrates how to make this Query updateable.
You need to remove the Join from SupplierID.
Let me know if this works for your database.

When I set it up like that it doesn't fill the PurchasePrice. I added the PurchaseID to the form as a check, but it doesn't fill that one in either. Something is wrong with my tables / relations and I can't figure out what. Maybe I need to explain my database a little more:

My tables:
- ProductsT holds all products that can be sold with their salesprice. A product just appears once in this table.
- SupplierT holds all possible suppliers. A supplier only appears once in this table.
- PurchaseT shows which suppliers can supply what products and at what purchase price. 1 product from ProductsT can be in the PurchaseT table multiple times because multiple suppliers can supply the same product. Of course suppliers can be in PurchaseT multiple times.
- OrderT holds general order info like data, description, ordernumber and customername. Of course an order only appears once in this table.
- OrderDetailsT stores the products for the orders in Order T. An order from OrderT can of course appear mulitple times in the OrderDetailsT table.

The OrderF form has the OrderDetailsF form as a subform. OrderF is based on OrderT and OrderDetailsF is based on OrderDetailsT.

In OrderDetailsF:
- I want to select the product from ProductsT and store ProductID in OrderDetailsT.ProductID
- I want to show the productname (doesn't need to be stored)
- I want to show the salesprice of a product (take it from ProductsT) and store it in OrderDetailsT to ensure historical pricing
- I want to add the amount of products needed for that order in OrderDetailsT.Amount
- I want to calculate amount*price = totalproductprice
- I want to select what supplier I get the product from and show the corresponding purchaseprice for that supplier from PurchaseT.

OrderDetailsF is based on OrderDetailsT

Base OrderDetailF off the Query, not the Table.

I just played around with it a little more and found my mistake. I changed the table relations and used the PurchaseID to link OrderDetailsT and PurchaseT (which I hadn't before). Then your solution worked correctly.

Thanks for your help!

Great that you got it working.