Solved Update tables trough vba (1 Viewer)

slharman1

Member
Local time
Today, 11:12
Joined
Mar 8, 2021
Messages
379
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?
Yes, I get it. But no can seem to answer my question about putting a current price into a quote and not having it change. EVER! Unless I want to update it. I’m sure there is a way. I just don’t know anything other than dlookup. Also I don’t know how to get the calculated data on the quote form as a price since it is not the same structure and the tables the calculations are based on. Or maybe I’m just to inexperienced to write the query correctly. It takes a lot of calculations and 3 tables to get the calculated sale price of a custom made product. But I do have it on a form. Just not sure how to put it into a field on the quote form and keep it there. If you have any suggestions I would love to hear them. Thanks again for all the help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:12
Joined
Oct 29, 2018
Messages
17,154
Yes, I get it. But no can seem to answer my question about putting a current price into a quote and not having it change. EVER! Unless I want to update it. I’m sure there is a way. I just don’t know anything other than dlookup. Also I don’t know how to get the calculated data on the quote form as a price since it is not the same structure and the tables the calculations are based on. Or maybe I’m just to inexperienced to write the query correctly. It takes a lot of calculations and 3 tables to get the calculated sale price of a custom made product. But I do have it on a form. Just not sure how to put it into a field on the quote form and keep it there. If you have any suggestions I would love to hear them. Thanks again for all the help.
Hi. I believe you did receive a couple of answers on how to automatically update your stored calculated values, perhaps just not in the details you would prefer. For example, I specifically told you that you can use a button to do all the work for you, I just didn't elaborate on what code you can use for that button. And the reason for that is I would prefer you would ask me how to avoid storing the prices in the estimates and yet still pull the accurate information into your forms rather than asking for the code to execute all your update queries.

Like I said above, how come you're not asking us how to automatically update the customer names and addresses in your estimates?

PS. Please do not take what I am telling you as anything other than we're just trying to steer you into a better direction. If all of us are saying the same thing that you don't want to hear, please consider that maybe we just haven't been able to make you understand or see exactly what it is we're trying to tell you. We are all here to help you, and part of that is to make you realize if there's a better approach to what you're currently doing.
 

slharman1

Member
Local time
Today, 11:12
Joined
Mar 8, 2021
Messages
379
Hi. I believe you did receive a couple of answers on how to automatically update your stored calculated values, perhaps just not in the details you would prefer. For example, I specifically told you that you can use a button to do all the work for you, I just didn't elaborate on what code you can use for that button. And the reason for that is I would prefer you would ask me how to avoid storing the prices in the estimates and yet still pull the accurate information into your forms rather than asking for the code to execute all your update queries.

Like I said above, how come you're not asking us how to automatically update the customer names and addresses in your estimates?

PS. Please do not take what I am telling you as anything other than we're just trying to steer you into a better direction. If all of us are saying the same thing that you don't want to hear, please consider that maybe we just haven't been able to make you understand or see exactly what it is we're trying to tell you. We are all here to help you, and part of that is to make you realize if there's a better approach to what you're currently doing.
Oh yes I understand. I am not the slightest bit upset with any of you guys, it’s me that doesn’t understand what I am doing. Maybe I am asking wrong question. What I want to do is have the calculated field from my estimate form (have you seen the DB I posted) populate on the quote form and then not update. Maybe i need to change the data I query on my query on my combo box on in my quote form. Just not sure how to do it. Any help and “steering” me to learn more is greatly appreciated. Everything I have read on DB design tells me I am doing it wrong. But don’t understand how to get there. Try me again. This time speak very very slowly. 😊
 

slharman1

Member
Local time
Today, 11:12
Joined
Mar 8, 2021
Messages
379
Hi. I believe you did receive a couple of answers on how to automatically update your stored calculated values, perhaps just not in the details you would prefer. For example, I specifically told you that you can use a button to do all the work for you, I just didn't elaborate on what code you can use for that button. And the reason for that is I would prefer you would ask me how to avoid storing the prices in the estimates and yet still pull the accurate information into your forms rather than asking for the code to execute all your update queries.

Like I said above, how come you're not asking us how to automatically update the customer names and addresses in your estimates?

PS. Please do not take what I am telling you as anything other than we're just trying to steer you into a better direction. If all of us are saying the same thing that you don't want to hear, please consider that maybe we just haven't been able to make you understand or see exactly what it is we're trying to tell you. We are all here to help you, and part of that is to make you realize if there's a better approach to what you're currently doing.
Actually my original reason for posting this thread was to figure out how to update my pricing on ALL of my estimates. That is solved. I used an update query for that. But after receiving all the feedback about storing my calculated field for later use (which I completely understand is not good practice) I am looking for the correct solution to this. now the problem becomes how to get the calculated field from one form along with the description onto another form that doesn’t get updated when I open the form again.
 

slharman1

Member
Local time
Today, 11:12
Joined
Mar 8, 2021
Messages
379
Hi. I believe you did receive a couple of answers on how to automatically update your stored calculated values, perhaps just not in the details you would prefer. For example, I specifically told you that you can use a button to do all the work for you, I just didn't elaborate on what code you can use for that button. And the reason for that is I would prefer you would ask me how to avoid storing the prices in the estimates and yet still pull the accurate information into your forms rather than asking for the code to execute all your update queries.

Like I said above, how come you're not asking us how to automatically update the customer names and addresses in your estimates?

PS. Please do not take what I am telling you as anything other than we're just trying to steer you into a better direction. If all of us are saying the same thing that you don't want to hear, please consider that maybe we just haven't been able to make you understand or see exactly what it is we're trying to tell you. We are all here to help you, and part of that is to make you realize if there's a better approach to what you're currently doing.
By the way there are no customers or addresses in my estimates. And I get it that I am still storing a calculation on my quote. But it has to be that way. What I don’t want to do is store a calculation in my estimate.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:12
Joined
Oct 29, 2018
Messages
17,154
By the way there are no customers or addresses in my estimates. And I get it that I am still storing a calculation on my quote. But it has to be that way. What I don’t want to do is store a calculation in my estimate.
Yeah, none of us were having any problems with your quotes - it's just the estimates we were discussing with you here. I haven't looked at your db. I'll do that as soon as I get a chance.

It's good that you're not storing names and addresses, but you didn't specify how you can tell which customer the quote belongs. If I would take a guess, you're probably using a CustomerID, correct? If so, that's what we're trying to say with your estimates. Rather than store the Price, store the ProductID.
 

slharman1

Member
Local time
Today, 11:12
Joined
Mar 8, 2021
Messages
379
Yeah, none of us were having any problems with your quotes - it's just the estimates we were discussing with you here. I haven't looked at your db. I'll do that as soon as I get a chance.

It's good that you're not storing names and addresses, but you didn't specify how you can tell which customer the quote belongs. If I would take a guess, you're probably using a CustomerID, correct? If so, that's what we're trying to say with your estimates. Rather than store the Price, store the ProductID.
I think you’ll see my problem when you look at the DB bob asked me to post.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:12
Joined
Feb 19, 2002
Messages
33,747
I went into detail on the difference between storing the value and getting it live. Getting it live (using a join) doesn't work for you because you don't want every estimate to be updated. Therefore, the method suggested - run an update query that updates ONLY the estimates that can be updated is the correct solution. But, you are also storing actual calculated values. THOSE should be calculated in the query instead of being saved in the record.
 

slharman1

Member
Local time
Today, 11:12
Joined
Mar 8, 2021
Messages
379
I went into detail on the difference between storing the value and getting it live. Getting it live (using a join) doesn't work for you because you don't want every estimate to be updated. Therefore, the method suggested - run an update query that updates ONLY the estimates that can be updated is the correct solution. But, you are also storing actual calculated values. THOSE should be calculated in the query instead of being saved in the record.
I do want all estimates to be updated. Every one. Just don’t want the amount on the quotes to be updated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:12
Joined
Feb 19, 2002
Messages
33,747
WHAICHEVER - If you want ALL of them to be updated when a price changes - DO NOT STORE THE PRICE. Period. If you want to control whether the price changes or not, do it with a query in the AfterUpdate event of the FORM where the price changes.
 

Users who are viewing this thread

Top Bottom