A few things
1. I still don't see what you are trying to do. I think you may need to rethink your data structure. See below comments
2. You should only need to store the material ID in your stock table. The whole point of relational databases is to avoid repeating data entry where it is not necessary. You can perform lookups by just storing the primary key/foreign key in any of your tables.
3. You'll need to make some small changes to your form. I am attaching a modified version of your database as well I have made a query qryStock connecting your stock to your material table to look up the part number, code and name, my assumption is that one of these tables will hold the 'base' price information per unit and one will be holding the actual orders but, again, this needs to be rethought
Your table structure should be something closer to:
Code:
tblPart
P_ID P_Number P_BasePrice P_Description -----> other part related fields
1 PCK8297 22.33 FILTER KARZBINE
2 7PK1795 16.01 REBRASTI JERMEN
tblCustomer
C_ID C_Name ---> other customer related fields
1 Customer A
2 Customer B
tblStore
S_ID C_ID S_Name S_Address S_Phone ----> other store related fields
1 1 Store A 1 1st St 111111111
2 2 Store B 2 1st St 222222222
tblOrder
O_ID O_Date O_ShipDate C_ID
1 1/1/2019 5/1/2019 1
2 1/1/2019 4/1/2019 2
tblOrderDetail
OD_ID O_ID P_ID P_Qty P_Cost S_ID
1 1 1 5 111.66 1
2 1 2 10 160.10 1
This'll give you more flexibility to ship items to different stores on the same order then you can do things like apply a percentage discount to an order etc based on the base unit cost of the material.
DatabaseE.zip