It would be possible by using the DLookup command somewhat like this:
[VBA]
Forms!NordersMaken.NordersDetails!LocationId = DLookup("[LocationId]","Products","ProductId = " & Me.ProductId)
[/VBA]

This is assuming your ProductId field holds a numerical value; you will have to use extra quotes somehow if it's a text value. Also, you would need to duplicate this line of code in ProductId's Exit event if you want the LocationId field to be updated if the user manually changes the ProductId. As you can see, the DLookup command can be awkward and complicated, not to mention slower to run on the machine.

As a personal design preference, I would probably avoid including those extra fields altogether, at least for your data entry form. The NordersMaken form and its NordersDetails subform are there to add new records to the Orders and Order Details tables respectively. But only a single field (ProductId) on the Order Details table has anything to do with the Products table, and so ProductId should be the only thing the user needs to enter. If you wanted to load extra information from the Products table simply for confirmation purposes (assuring the user he has selected the proper ID), the simplest method of achieving this is by using a multicolumn combo box as the control for the ProductId field. You can design the combo box to display the fields you want from the Products table and use the ProductId field as the bound column.

For the purposes of viewing previous orders, I would create a different form entirely. You can base the form off a query (using the relationships between all three tables) and get all the Detail and Product information for each order perfectly with little fuss, just as if everything was part of a single record in one big mega-table.