Let's step back and take a larger view of database design. One of the things that a properly normalized schema avoids is storing the same piece of data in multiple places. For example, you don't store the customer name in the Order table because at some point, the customer name might change and you don't want to have to update all your old records if the name changes. What you do store is the CustomeriD because it will never change. It is a way of uniquely identifying a customer and when you print the order or view it, you use a queyr that joins the Order table to the Customer table so you have the current customer name available when you need it.
That said, there are reasons for storing data in multiple places. You do that when you specifically do NOT want the "second" value changed when you update the primary source. Using our Order entry example, when you add a product to the Order Details table, you copy the price at the time of sale and save it in the Order Details table. That way, from now until eternity, the Order has the price of the product at the time of sale and this seems to be what you have done. You copied the price to your estimate record. In the world of orders, sometimes an order is placed but not immediately shipped. Common business practice is to update the price in the order if the product price decreases before the order is shipped but to not update it if the price increases. This is done in the form that changes the price in the AfterUpdate event of the form. You need an internal flag because you need to give yourself a reminder that you need to run an update query after the price is updated when the new price is less than the old price (compare Me.Price to Me.Price.OldValue). You would do this In the FORM's AfterUpdate event by checking the flag (which you set in the FORM's BeforeUpdate event) that tells you that the price was reduced. then you run an update query that selects all unshipped records for this product and update the price to the new one. This is the method that was suggested to you by the others.
However, if you ALWAYS want the price to change in the estimates table regardless of when the estimate was made even if it never resulted in an order, then you would NOT duplicate the price in the estimate record. You would always pick up the current price by using a query that joins to the product table. If you only want to update "active" estimates, then you could store the price as you are doing but update the "active" estimates by running an update query when the price changes.
After re-examining everything and thinking about my design. I have come to the conclusion that this is the problem:
I am using a calculated field on my estimate form to get my estimate price by adding the labor and raw materials together (with some added calculations) to get the estimate price.
Due to my lack of knowledge in database queries/design, I have created a field in the estimate table Named EstPrice.
I put it on my estimate form and set it to be equal to my calculated field that figures the estimate price.
What happens is that it does not update my EstPrice field unless I open the estimate form where the data is input to the field via being equal to my calculation and recalculate the record.
I guess another way to say it is that: My price for my product is a calculation that can change regularly.
When I quote an order I pick a product from a combo box, but the price is not the current price unless I open the estimate form so the EstPrice field can be updated to match the latest price of the raw materials.
So I guess what I really need to do is figure out how to update the estimate price even if the estimate form is not opened.
I could really use some help with that.
I covered this in my explanation. You need a way to identify the estimates that can be changed. Then each time you change a product price, you would run an update query that selects all the estimates for that product and changes the price.
There is no need to "cycle through" anything. Update queries are the way to do bulk updates. If you can identify the estimate you want to change using VBA, you can identify it using criteria in a query. If you don't currently have a way, that is what you need to start with. Adding a field to the table that you can use to identify which estimates can be updated is the first step.
thanks Pat, an update query did the trick. Know to write the code using SQL statement and a progress bar. Although right now it takes about 1 second.
thanks again for your help.