Storing a calculation

[Ruegen]

I've made a query that calculates the current rate (which is pulled from another table) as a total with the price entered in the table on the form.



I'd like to store that calculation total so that later I can store that calculation (using a dlookup after update?) when I make a booking on another form.

That way I can change the price without it affecting the booking price...

My question is how do I store that calculation form the query into the table field?



As I type the "Cost Price pp" is the query and it updates with the total. However as it is a query it isn't storing the information anywhere. (I'd like the price total to be the stored field of whatever Cost price pp is). So it should say $3.60 too. (eventually I will remove/hide Cost Price pp from the form)

[June7]

Saving a calculated value will require code. Review http://allenbrowne.com/casu-14.html

[Ruegen]

So I am guessing?

Private Sub TotalPrice_AfterUpdate()
Call CostPricePP_AfterUpdate
End Sub

CostPricePP is not a table field...

[June7]

No, I don't think that is correct for your situation. What data are you typing? The 3.30? Then in the AfterUpdate event of that textbox, have an expression that sets the value of the other textbox (step 3 of Allen's example), which should be bound to whatever field you want the calculated value saved to. You don't need steps 4 and 5.

I am confused by description of db setup. Is the query the RecordSource for a form? What field in what table do you want to save what value?

If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved in the issue.

[Ruegen]

I have the form with a query as a record source, in that query I have made a calculated field which grabs the value from a different table, calculates it with a field in the query and shows the result in the query.



so as you can see CalcGstPrice is not in the original table. It's so that I can take whatever result is in another table and calculate it against whatever is in this table "Shows".

Then I just displayed the query field "CalcGstPrice" on the form - which is great. However now I want to store that value.

As I understand it - and I probably don't - you are suggesting instead of making a query field "CalcGstPrice" I should apply an expression to a text field on the form and do the calculation in that instead....

If it was as simple as saying... "whatever CalcGstPrice is, make PriceTotal that too"

[June7]

If PriceTotal is the field that should have the calculated result, then yes, it is simple, However, don't think I would save that. I would save the value that is returned by the lookup. Then calculate the total when needed on forms and reports. That is in keeping with principle of 'save raw data, do calcs on reports'.

[Ruegen]

ah, well I see your point - I could keep the query calc for reports but still it would be wise to make a field do an expression calculation.

My question is will the lookup get the field from the other like a query will? I figure AfterUpdate [PriceTotal] will = whatever [CostPriceExGST] is * [tblGST]![GSTPercent]?

Update:
If I add to the froms record source the other fields of other tables linked in a query then I can hopefully calculate those in expression right?

I'm a bit confused by the link provided from you as to what field I should be applying which afterupdate.

I have the fields:

CostPriceExGST
GSTPercent
PriceTotal

I am guessing I am applying/tying the calculation expression to the PriceTotal field on the form? or put the call on it?

[Ruegen]

Private Sub CalcField_AfterUpdate()
Me.CalcGstPrice = IIf([GSTRegistered] = True, Round([CostPriceExGST] + ([CostPriceExGST] / [GSTcalc ]), 1), [CostPriceExGST])
End Sub


Private Sub PriceTotal_AfterUpdate()
Call CalcField_AfterUpdate
End Sub

[June7]

Allen Browne's example uses two fields that require user input. Those inputs are used in a calculation. If either is changed then the calculated result must change. Allen's code shows a way to effect that revised calc if either of the raw data fields is changed. If I understand correctly, you have only one field for user input and then another value is retrieved by a lookup. So only one AfterUpdate event is involved in your situation, the textbox that requires user input. I can't see the expression in your image so I don't know how you do the 'lookup', if that takes place in the expression.

I am not clear on which textbox is the user input and how you do the 'lookup' and what it is you lookup.

[Ruegen]

on tblTradingAs you have "GSTRegistered"

on tblGST you have "GSTcalc"

on tblShows you have "CostPriceExGST" and also "TotalPrice"

on the form I could apply the code on afterupdate to the field for CostPriceExGST (user input) which calculated after input and puts that calculation in TotalPrice (which it then displays)

[June7]

Three tables are involved in this? I am more confused.

What is the raw data that user must enter that is required for the calculation?

It is the raw data user input textbox AfterUpdate event that needs code. That is the example provided by Allen Browne - through step 3.

[Ruegen]

Yeah table 1 is tblTradingAs - that determines whether the price gets GST added to it or not, so if they are registered for GST then it gets calculated with/without it.

table 2 is tblGST that is where the current rate of GST is entered for all shows.

table 3 - tblShows is where you make the show, add the trading company, put in the price of the show (via CostPriceExGST) and then there is an if for the registered part - my query grabs the checkbox result from tblTradingAs, then does calc) but of course it won't enter any data into the TotalPaid in the tblShows.

Baisically if I can have the user enter in CostPriceExGst the price, the TotalPrice field will get populated with the "CostPriceExGst" Price + GST".

[June7]

Try setting the form RecordSource to a query that includes all 3 tables. The JOIN type will be "Include all records from tblShows ..." That will make the related data available for use in expressions but don't allow edits to the lookup tables (tblTradingAs, tblGST).

Is GSTRegistered field a Yes/No?

If I understand, user enters value into CostPriceExGST then depending GSTRegistered field, calculate with GSTcalc value. Then code in that textbox AfterUpdate event to save either GST value or calculated result, maybe something like:

If Me!GSTRegistered = True Then
Me!GST = Me!GSTcalc
End If

Or

Me!TotalPrice = CostPriceExGST + IIf(Me!GSTRegistered = True, GSTcalc, 0)

[Ruegen]

Brilliant - working only the formula is wrong - I keep trying different things. Because it isn't reading the format of % it just ads 10 to the CostPriceExGST

PriceTotal = IIf(Me!GSTRegistered = True, CostPriceExGST + GSTPercent, CostPriceExGST)

I know mine is wrong however

I want to PriceTotal to be the CostPriceExGst + 10% if true else just CostPriceExGST

I need to Format() the percent I guess but didn't seem to work...

[June7]

Sorry, didn't realize it was a percent, try:

PriceTotal = CostPriceExGST + IIf(Me!GSTRegistered = True, CostPriceExGST * GSTPercent, 0)