Form Field Disappears When Related Value Changes

[MRhodes]

Good day,
I am an absolute beginner in Access and was thrown into a job with no training. Any help is much appreciated. I am using Access 2003 and trying to create a new Form for meal recipes, but I’m having trouble losing values when ingredient prices change.
I have a Product table which holds all my kitchen ingredients (item#, name, quantity on hand, cost, etc.).
I have created a query to list all product details.


I have created a new Table called “Recipes” which holds a recipe name and ID# for each record, as well as quantity, name, cost of each ingredient to make a recipe.
I have created a “recipe” form with 15 rows for ingredients, each of which allows me to enter a quantity, select a product from a Combo Box, and the price field automatically fills in for that item. Everything in the form works beautifully when entering a new recipe or looking up an existing one.
However, my problem is that if a product cost changes in the “Product” table, the new cost is not reflected in the “Recipes” Table or Form. For example, when I created a recipe for salad, the lettuce cost was $5, the product cost has since changed to $7 in the “Products” table. The “Product” table, form, and query all show the correct $7, but the “Recipe” Table and Form still show $5.
I have tried making the Source of the Product Name Combo Box to be the “Products” Table as well as the “Product” Query, and both have the same issue.
I have tried Auto filling the Product Cost in two methods as well; one using an On Change Event on the Product field to fill in the Cost Field, the other method using the Control Source property between the Product Name and Cost Fields. Both have the same result.
Unfortunately, my standalone computer with Access has no internet, so I can’t share screenshots or code. Hopefully my description was thorough enough to understand. Any help is appreciated, thank you all.

[June7]

Advise not to use space nor punctuation/special characters in naming convention. Better would be ItemNo or ItemNum, QtyOnHand.
Also, name is a reserved word and should not use reserved words as names.

Saving quantity on hand is usually not advised. Calculate quantity balances based on transaction records. Review http://allenbrowne.com/AppInventory.html

If you save the cost value into recipe record, then changing cost in Product table will not affect saved value. If you want recipe to always reflect the current cost, don't save it into Recipe record, retrieve it by joining tables in report. This is how relational database is supposed to work - don't duplicate data.

The combobox should show the changed cost value. What is the SQL statement for this combobox? First you said Product query shows $7 then you say has issue.

Can you save db to a USB stick then go to a computer that has internet to upload here?