Choose a field contents from a related table and display the corresponding value in another field
My database has among others two tables, Materials & Products. The Materials table is where we store stock information in the field Stock_Number and calculate its square footage and price based on what we pay for the material and store this in the "Cost_Per_SqFt" field. I want to be able to build products in the Products table by selecting the appropriate Stock_Number from a drop down list and have the corresponding Cost Per SqFt show in the field in the Products table.
I am able to access the Stock numbers from a dropdown list but for the life of me I cannot get it to display in the related "Cost_Per_SqFt" field in the Products table.
Any help would be greatly appreciated.