Overwrite a calculated field

[Guest]

Hi,
I have a situation where a quote is generated based on a cost * quantity and
then multiplied by a margin. I have done this in a query.
The query is then the basis of a subform.
Now sometimes there is a need to either discount or add a premium to the
quote.

Hah, as I'm writing this I think I can see the solution.
Should I add another two calculated fields for 'Discount' and then the
resultant final 'Quote' on that (I'm aware that you don't want to calculate
calculated fields and need to use the 'original' fields) or is it possible to
overwrite a calculated field on a form?

Cheers,

Graeme

 

[Jeff Boyce]

I'm not aware of a way to "overwrite a calculated field on a form". I'm not
clear on your need to do so.

If you need a (re-)calculated value, based on discount/premium, couldn't you
add a control on the form to hold either the amount or the percentage (+/-),
and re-calc/re-display the result?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff,
yeah, that's pretty much the conclusion I came to.
It seems that pulling calculated values from queries into forms is sometimes
not that easy when creating subforms. Why is that?
If I'm using the query for other forms then surely that is the way to go
rather than put the calculation onto a control on a form?

Cheers,

Graeme

 

[John W. Vinson]

Hah, as I'm writing this I think I can see the solution.
Should I add another two calculated fields for 'Discount' and then the
resultant final 'Quote' on that (I'm aware that you don't want to calculate
calculated fields and need to use the 'original' fields) or is it possible to
overwrite a calculated field on a form?

Well... just a clarification to the advice "never store calculated fields".
That should be "never store fields if you know for sure that you can calculate
them".

If you want the ability to override the calculation, then the calculated field
is actually just a default value, and you DO need to store it. You can do so
using a suitable Form event - the form's BeforeUpdate, or the AfterUpdate
events of the controls which go into the calculation.

John W. Vinson [MVP]

 

[Guest]

Cheers John,
you don't have any examples that I could use?
I'm not that good with the VB stuff (I presume that's what I'd use?).

Thanks,

Graeme

 

[John W. Vinson]

Cheers John,
you don't have any examples that I could use?
I'm not that good with the VB stuff (I presume that's what I'd use?).

Sure, I could post some sample code. Please post the names of the relevant
controls on your form, and describe just what you want to happen and when -
e.g. how will the discount be entered?

John W. Vinson [MVP]

 

[Guest]

Thanks John,
there are actually two requirements:
(1) Simple cost calculation - [Cost.PriceCharged] * [Cost.Quantity] =
TotalCost
(2) A quote calculation - [Product.CostPrice] * [For.Units] = QuoteCost
QuoteCost * [For.Discount] = Discount Quote

Hope that makes sense!

Cheers,

Graeme

 

[John W. Vinson]

Thanks John,
there are actually two requirements:
(1) Simple cost calculation - [Cost.PriceCharged] * [Cost.Quantity] =
TotalCost

In the AfterUpdate events of *BOTH* the PriceCharged and Quantity fields:

Private Sub PriceCharged_AfterUpdate() ' or Quantity_AfterUpdate
If Not IsNull(Me.[PriceCharged]) _
And Not IsNull(Me.[Quantity]) _
And IsNull(Me.[TotalCost]) Then
Me.TotalCost = Me.PriceCharged * Me.Quantity
End If
End Sub

This checks to see if the source fields PriceCharged and Quantity have data,
*and* the TotalCost field is empty (so as not to stomp on an
already-overridden value), and pushes the data into the control. It does
assume that you have Textboxes on the form named PriceCharged, Quantity, and
TotalCost - if the names of the textboxes differ from the names of the table
fields, use the names of the textboxes instead. In fact it's probably a good
idea to CHANGE the names of the textboxes (say to txtPriceCharged and the
like) if they *are* the same.

Note also that if the table is named Cost, then the correct syntax for
referencing a table fieldname is [Cost].[PriceCharged]. Putting the period
inside the square brackets tells Access that you have a Control or a Field
actually *named* Cost.PriceCharged - whereas you actually have two named
objects, a table named Cost and a field named PriceCharged.

(2) A quote calculation - [Product.CostPrice] * [For.Units] = QuoteCost
QuoteCost * [For.Discount] = Discount Quote

Apply the same logic.

John W. Vinson [MVP]

 

[Guest]

Thanks John,
much appreciated

Thanks John,
there are actually two requirements:
(1) Simple cost calculation - [Cost.PriceCharged] * [Cost.Quantity] =
TotalCost

In the AfterUpdate events of *BOTH* the PriceCharged and Quantity fields:

Private Sub PriceCharged_AfterUpdate() ' or Quantity_AfterUpdate
If Not IsNull(Me.[PriceCharged]) _
And Not IsNull(Me.[Quantity]) _
And IsNull(Me.[TotalCost]) Then
Me.TotalCost = Me.PriceCharged * Me.Quantity
End If
End Sub

This checks to see if the source fields PriceCharged and Quantity have data,
*and* the TotalCost field is empty (so as not to stomp on an
already-overridden value), and pushes the data into the control. It does
assume that you have Textboxes on the form named PriceCharged, Quantity, and
TotalCost - if the names of the textboxes differ from the names of the table
fields, use the names of the textboxes instead. In fact it's probably a good
idea to CHANGE the names of the textboxes (say to txtPriceCharged and the
like) if they *are* the same.

Note also that if the table is named Cost, then the correct syntax for
referencing a table fieldname is [Cost].[PriceCharged]. Putting the period
inside the square brackets tells Access that you have a Control or a Field
actually *named* Cost.PriceCharged - whereas you actually have two named
objects, a table named Cost and a field named PriceCharged.

(2) A quote calculation - [Product.CostPrice] * [For.Units] = QuoteCost
QuoteCost * [For.Discount] = Discount Quote

Apply the same logic.

John W. Vinson [MVP]