Trouble using Calculate Fields in Update Query

[Toga]

I am having a problem with my Update Query running before my calculated fields have had a chance to update themselves.
As a result, the right data doesn't always get stored in the tables being updated.

Is there a way to verify or wait for the calculated field to update itself prior to the "DoCmd.RunSQL UPDATE...." statement being processed?

Me.Refresh doesn't seem to do the trick!

 

[JerryDennison]

I STRONGLY recommend you do not store calculated results in your table. As you've found out the results are not always positive. What are you trying to do?

 

[Toga]

I have a "Gross Weight", "Total Scrap" (Calculated) & "Net Weight" which is also calculated via subtracting the Total Scrap from the Gross Weight.

User enters in his gross amount and then in a subform, itemizes any scrap generated. The "Total Scrap" field is the sum of the entries made on this subform.

I simply want to take the results of those calculations and update the underlying tables....

Because this in going to be part of a production process, I have to update 3 tables. Relieve the amount from the backlog table that was used by this process, Update the record for this process step....Gross Wt & Net Wt, and then update the amount available for the next process step....which is where I am having my problem. I need to "Net Weight" to be the amount availabe for the next process step.....so that's why I am using the calculated value.

 

[Toga]

You know what.....the more I think about it, your probably right.....cause this approach is inconsitent as heck! I guess I was just trying to prevent doing a query on the scrap totals every time I just wanted to look at the Net Weight or a Yield (Net/Gross).

Thanks for your advice.....as I think it wil save me alot of pain down the road.

Thanks....Toga

All I can say is that it is hard to be a Rookie sometimes!

 

[blouie]

Hey Toga,

The fact that you are trying to write a field value to multiple tables raises a red flag. In a relational database you want to avoid duplicate entries. You may want to try to rethink about how your multiple tables store the same data and how they can be streamlined.

If you need more info on this let me know. Luis.