Calculations on data entry 1

[abbyanu]

Hi

I'm making a small Access app. On the data entry screen, the user enters two fields, say quantity and unit price. On the same data entry screen, how can make the program automatically calculate the total price (which is = unit price*quantity, and save the result in the total price field of the table? Please help. Thanks.

 

[Rohdem]

It is generally a good idea not to store calculated values in a table, but rather to calculate them as they are needed in queries, forms, and reports. For example, if you wanted the total to be displayed on the form, you could use:

=[unit price] * quantity

as the control source for a text box.

If you feel that you MUST store the value in the table, use the after update event of the price and the quantity to recalculate the value. Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[JoeMiller]

WARNING! It is NEVER a good idea to store any value in any database that can be calculated whenever it's needed. A query can easily have a field added that will calculate the extended price for you on the fly which can be used in reports, on forms, etc...

If for some reason you MUST store the value, you can do this by a couple of different ways. The easiest would be to place some code in the AfterUpdate event of both your UnitPrice and Quantity field. This is what that code looks like:

ExtendedPrice = Val(UnitPrice) * Val(Quantity)

HTH Joe Miller
joe.miller@flotech.net

 

[InsaneProgrammer]

The following code will do what you need. Place it in the After Update event.

Private Sub Quantity_AfterUpdate()
If Not IsNull(Me.Price) and Not IsNull(Me.Quantity) Then
Me.Calculation = Me.Price * Me.Quantity
End If
End Sub

 

[MichaelRed]

Hmmmmmmmmmmmm,

Joe,

"WARNING! It is NEVER a good idea to store any value in any database that can be calculated whenever it's needed"

The point is that in MANY retail apps, the "Price" for an item may repeatedly change. If you need to accumulate sames over any period, it is easier to calculate the 'extended price' at the time of the sale than 'reconstruct' the sale transaction. Like many programmers, I have attempted to adhere to the 'rule', only to have some change in the underlying table(s) be changed after a transaction - and cause untold numbers of houurs of grief and angst attempting to un-do some change - ayt least long enough to then copy more fields into the sales transaction table,




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JoeMiller]

Michael:

My statement was meant to be applied to storing the ExtendedPrice only as it's own field. If you store the UnitPrice at the time of the sale you still don't need to store the ExtendedPrice because it can still be calculated. I'm aware of having to maintain historical pricing because changes in price occur daily in many industries. Sorry if I wasn't clear.

Joe Miller
joe.miller@flotech.net

 

[abbyanu]

Thanks so much, folks, for your assistance. God bless. Abby.

 

[abbyanu]

Thanks so much, folks, for your assistance. I'll heed your advice and not store the results of the calculations in the table. My dilemma actually is as highlighted by Joe - historical pricing. Keep the faith and God bless.