Solved Update tables trough vba (1 Viewer)

[slharman1]

I have a parts table that contains prices for parts I purchase, then I have an estimate table with related child tables that contain the parts and quantities of the parts to make an estimate for custom made products. I need to figure out how to update ALL of the products to the current price when I update the parts prices in the parts table. so I need a form with a button on it (I will call the form "Update Estimate Pricing" or something like that) so when I hit the button it requeries/refreshes all of the estimate table records and it's child table records to the updated prices from the product table. Also I would like the "date modified" field from the estimate table to be updated to the date I run the code.
I can figure out where to start.

 

[Ranman256]

you wouldnt run code, you would run update queries.

 

[gemma-the-husky]

Do you mean you want to change a selling price because the base cost has changed, or change the cost price that's held (duplicated) in the estimating section?

 

[jdraw]

Do you plan to update the existing records with new values OR
keep the existing values and append new records with the revised prices?

If you simply replace the values, and, if you run any historical reports/queries, the new values will be used in such queries. Bottom line is you have wiped out/overwritten any history. You need to prudently use EffectiveDates for Product prices or include the then current Price/AgreedUponPrice involved in a sale or purchase in a transaction to provide an audit of prices on historic records.

 

[slharman1]

Do you mean you want to change a selling price because the base cost has changed, or change the cost price that's held (duplicated) in the estimating section?

I will update prices in the parts table by hand, one at a time (I get a quote from my supplier with new price) then I need to automatically run through each estimate record and it’s child records and update the sell cost that is figured with formulas on its form based on the new parts pricing that was updated in the parts table. Right now I have to open each estimate record and it re calculates the price when opened. I want some query or something I can run that will go through all estimates and update them all to the new price held in the parts table.

 

[slharman1]

Do you plan to update the existing records with new values OR
keep the existing values and append new records with the revised prices?

If you simply replace the values, and, if you run any historical reports/queries, the new values will be used in such queries. Bottom line is you have wiped out/overwritten any history. You need to prudently use EffectiveDates for Product prices or include the then current Price/AgreedUponPrice involved in a sale or purchase in a transaction to provide an audit of prices on historic records.

I plan to update the existing records with the new price. Don’t get confused with my term estimate. My customer doesn’t see my estimate. That is a product that I manufacture. Then I put that price on a quote to my custom (using a quote table). But yes I would like to record the date I run the update so I know when a particular estimate was last updated to current pricing. Thanks

 

[slharman1]

I don’t think I am really updating anything. I just want to requery or refresh all of the data in the estimate records and their child fields to refresh the records to the current price in the parts table. If that helps. So what I need the code (or query) to do is run through the recordset of estimates and it’s child tables and refresh the records.

 

[bob fitz]

I don’t think I am really updating anything. I just want to requery or refresh all of the data in the estimate records and their child fields to refresh the records to the current price in the parts table. If that helps. So what I need the code (or query) to do is run through the recordset of estimates and it’s child tables and refresh the records.

Can you post a copy of your db with just a couple of dummy records.

 

[theDBguy]

I plan to update the existing records with the new price. Don’t get confused with my term estimate. My customer doesn’t see my estimate. That is a product that I manufacture. Then I put that price on a quote to my custom (using a quote table). But yes I would like to record the date I run the update so I know when a particular estimate was last updated to current pricing. Thanks

I don’t think I am really updating anything. I just want to requery or refresh all of the data in the estimate records and their child fields to refresh the records to the current price in the parts table. If that helps. So what I need the code (or query) to do is run through the recordset of estimates and it’s child tables and refresh the records.

If you want the estimates to reflect the latest prices up until they become quotes, then you could save a lot of work if you don't store the prices in the estimate tables, just store them in the prices table. You can store them in the quotes table, since you don't want those updated. And if you want to know when the price changed, you can store the date in the prices table too.

Otherwise, to do what you want, you can execute a series of UPDATE queries using VBA when you click a button. But again, you can avoid all that work and know that the estimate prices are always up to date without having to remember clicking on a button.

 

[slharman1]

If you want the estimates to reflect the latest prices up until they become quotes, then you could save a lot of work if you don't store the prices in the estimate tables, just store them in the prices table. You can store them in the quotes table, since you don't want those updated. And if you want to know when the price changed, you can store the date in the prices table too.

Otherwise, to do what you want, you can execute a series of UPDATE queries using VBA when you click a button. But again, you can avoid all that work and know that the estimate prices are always up to date without having to remember clicking on a button.

The estimate table is not an estimate for the customer it is an estimate for me to get a price for a product we manufacture.

 

[slharman1]

See attached for sample DB, open the switchboard go to estimates - this is frmEstimatesList, dbl click on any estimate - this is the frmEstimates, now take a look at the estimate form. The text box below the sell price (SellPrice) gets it value from the formula in it as well as the TotalMaterial and TotalLabor txtBoxes
I want to have a query or code to run through the recordset (which must includes the child tables tblEstLaborDet and tblEstMatDet) for every record listed on the frmEstimateList from the previous page. of course the Estimate List is changing on a regular basis (adding more records or modifying current records.)

 

[Pat Hartman]

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.

 

[slharman1]

I build custom fabricated products.
I produce an estimate of what that product costs by taking multiple parts and multiple labor costs adding them up to come up with a sell price for a product.

Please all understand this: my quote table is different than the estimate table. COMPLETELY DIFFERENT! The only thing that the quote takes from the estimate is the price of the manufactured product I am building through the estimate form. It is populated on the quote by a dlookup and will not change no matter what the price of materials and labor do whether they go up or down.

The estimate table and it’s children hold the raw materials and labor that go into making the product that is in the estimate table. I DO NOT NEED HELP WITH THAT!

I just need to figure out how to cycle through all of my estimates and update the pricing of the raw materials.
In the market we are in raw material prices have been changing daily and I want to be able to update the estimate at any given time- such as when I update the price of raw materials.

I cannot change the price I have quoted to my customer until and if the expiration date of the quote passes and only if I choose to update it.

Then that estimate for that product that I create and build will be the new price for all future quotes.

Again if I can figure out how to cycle through my product estimates and update the raw material price that is what I need to do.

 

[Pat Hartman]

In the market we are in raw material prices have been changing daily and I want to be able to update the estimate at any given time- such as when I update the price of raw materials.

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.

 

[slharman1]

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.

 

[Pat Hartman]

It is poor practice to store calculated values. Doing this made your task more difficult than it needed to be. It is still not clear to me if when a price changes, you want to change ALL estimates but assuming you don't, then leave the price in the estimates table and use the update query to change the price. In the query that retrieves the estimates data, that is where you should have the calculation that extends the price.

At least you got the query to work But do try to understand my explanation of why you don't store "duplicate" data and why you don't store "calculated data and reading the explanations of the first three normal forms will be helpful since you violated two of them here

 

[slharman1]

It is poor practice to store calculated values. Doing this made your task more difficult than it needed to be. It is still not clear to me if when a price changes, you want to change ALL estimates but assuming you don't, then leave the price in the estimates table and use the update query to change the price. In the query that retrieves the estimates data, that is where you should have the calculation that extends the price.

At least you got the query to work But do try to understand my explanation of why you don't store "duplicate" data and why you don't store "calculated data and reading the explanations of the first three normal forms will be helpful since you violated two of them here

Pat, I don’t like storing a calculated field either but... how would get the form to update? It is based on to sub forms that hold the values. It might be that I am just looking at this wrong. A simple change to my actual quote that uses dlookup to get the price is the answer. I just didn’t want a ton of code and calculations on the quote form to have to run to get to the sell price. But now that I have learned more, that might be the way to go. What I have to make sure is that the calculated field that the quote would have to copy doesn’t get updated every time I open it and I don’t want the values for the material and labor in the quote form. Everything is working as it should and would be a simple change if I find a better solution but I am using this system daily and I am making more important modifications that are needed right now. I live in a fast paced industry and would definitely like this to be as smooth as possible. There are some things I don’t like about how I am using the dlookup. I just haven’t had time nor do I know a better solution right now, though I am learning daily. Any help is always greatly appreciated. Thanks again.

 

[theDBguy]

Pardon me for jumping in, but using DLookup() is typically slower than simply joining the lookup table in your record source.

Sent from phone...

 

[slharman1]

Pardon me for jumping in, but using DLookup() is typically slower than simply joining the lookup table in your record source.

Sent from phone...

That is how I would really like to get the data. But...how do I keep the quote from updating when I open it? Of course, the calculated field is not in the table either. That is why I chose to copy it to the table. Help is always appreciated, thanks DBguy!

 

[theDBguy]

That is how I would really like to get the data. But...how do I keep the quote from updating when I open it? Of course, the calculated field is not in the table either. That is why I chose to copy it to the table. Help is always appreciated, thanks DBguy!

The best part of not storing calculated values in a table is they'll never be wrong, and you won't need to update them, ever.

Let's think about it another way, do you keep personal information about your clients in a separate table? You know, their names and addresses in say a Customers table? If so, do you assign or use a Customer ID? If so, do you then use or store the Customer ID in the Quotes table? Or, do you copy the customer names and addresses from the Customer table into the Quotes table?