update calculation

[awake2424]

I am having trouble creating a VBA that will update a calculation in a specific field when a form opens. The form is called "Test" and basically I am trying to calculate a field TAT from Due_Date - Result_Date. The calculation works great as long as the user updates within access, a function (WorkDays is used), the problem is since the db is linked to sharepoint when the Result_Date is entered there, access only updates with the new value. It doesn't calculate TAT eventhough all the needed values are there. So if upon opening the form if it was updated that may solve the problem. Thank you.

Example, Due_Date = 10/2/2014 and Result_Date = 10/6/2014, TAT = 3 (weekends and holidays excluded by the function). Thanks .

 

[plog]

That's why you don't store calculated values. Instead you should create a query to do the calculation and then reference the query when you need that value. A calculation shouldn't be stored, it should be calculated.

 

[namliam]

Havent we covered this in a previous thread?

 

[awake2424]

My previous thread was related to this, but was relating to the function. Now that is working fine from within the db, it is just a matter of handling values that are from outside the db and using them. Thank you .

 

[namliam]

I think I told you (or I am mixing things up since this is starting to get on auto-repeat)

We do not store values in tables, therefor we do not update them.
You calculate things on the fly if and when you need them in a query, form or report.

NEVER do we store calculations in tables, NEVER...
Then again a few exceptions may exist but I do say NEVER EVER EVER do we store calculated values.... unless there is a very very very very Very VERY good reason to do so

 

[awake2424]

This might be one of those situations as we use the value in this calculated field to track over a 3-month period. If I update the field within access it works perfectly.

The problem is often times it is updated outside of access through a linked connection. I don't know the best way to handle this (thought maybe an update button was a start), but I'll leave it up to the experts. Thank you very much .

 

[namliam]

no the calculation is simple enough to repeat it on the spot, only reason feasable may be the outside connection/update....

If that update can/should overrule the (standard) calculation then yes that may be a reason to actually store it... even then you can store the manual value and have it supperseed your calculation.... your need to (re)update the column though suggest this isnt the case....

Rare, very rare are the proper times to actually store a calculated value, this I dont think is one of them

if you are married to the stored value against all odds, an update query may do the trick to mass update the field back to the calculation...
Or indeed an update button on the form to recalc it...

 

[awake2424]

Say there are 1000 records on a form (Test). Is it possible to create a button or prompt the user for specific records to update?

For example, when the button is clicked or the form is opened a prompt "Any records to recalculate" appears. The user enters 800-1000 and clicks ok.

Then,

 Me.TAT = WorkingDays(Due_Date, Result_Date)

and the TAT field in the selected records are updated. Thank you .

 

[namliam]

Yes that is possible, but WHY do you keep incisting on storing this calculated value?
1 in probably a 1.000.000 should actually store a calculated value, why would you be that one?

If a user can choose to "randomly" reset the calculation, then there isnt really a need to store this... Simply calculate it on the fly in a query or on your form.

You can if you choose use an update query to reset your calculated values, using this update query you can set whatever where clause you want on recieveddate or on your key.