Calculation In A Form For The New Guy

Calculation In A Form For The New Guy

Post by U3RlZmF » Wed, 15 Aug 2007 23:22:05

I am an admitted novice and need some help in getting my form to do what I
want in relation to the table.

I have a field labeled "5% Option" What I want this field to do is
calculate 5% of another field labeled "Total Cost". I have done this in the
control source of the former but it does not update when I enter the "Total
Cost" figure. I have to proceed to the next record and go back before i can
see the update.

Furthermore what I really want to do with the "5% Option" is this:

"If [Total Cost] is greater than $350.00, then multiply that figure *.05 If
Not Don't Calculate This Field and leave at 0" And I would like it to update
as the person enters a figure in the "Total Cost" Can anyone help me with an
expression for this and where to put said expression? These self-help books
are killing me with vagueness.

I really appreciate someones help.

Calculation In A Form For The New Guy

Post by Jeff Boyc » Thu, 16 Aug 2007 00:06:08


It sounds like you want the [5% Option] control to update after the [Total
Cost] control is updated.

One way to do this is to put an event procedure in the AfterUpdate event of
the [Total Cost] control. It might be something like:

Me![5% Option] = IIF([Total Cost] > 350, [Total Cost] * .05,0)

This way, after any change to the amount in the [Total Cost] control, the
[5%...] control is updated.

NOTE: this does NOT recalculate the [5%...] control when you get an
existing record. For that, you'll need to add something to the form's
OnCurrent event procedure, something like:

Call 5% Option_AfterUpdate()

NOTE2: the previous line probably will confuse Access, because of the space
after "%", and because of the "%". Consider renaming your control to
something that doesn't have a space and doesn't use that special character.


Jeff Boyce
Microsoft Office/Access MVP


Calculation In A Form For The New Guy

Post by ruralguy v » Thu, 16 Aug 2007 00:20:36

Hi Stefan,
You have probably read this before but it if wasteful and often problamatic
to store calculations in a table. It would be easy to get a query to give
you the value you need.
YourOption:=IIF([Total Cost]>350,[Total Cost]*.05,0)

Here's a link you should read:
Special characters that you must avoid when you work with Access databases

HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via

Calculation In A Form For The New Guy

Post by U3RlZmF » Thu, 16 Aug 2007 03:10:01


When I put that event procedure in it give me a prompt to create and save a
macro and then it does nothing in the [5% Category] What step am I missing?
Can you help me finish this out? I greatly appreciate your help.


Calculation In A Form For The New Guy

Post by Jeff Boyc » Thu, 16 Aug 2007 03:24:17


I'll reinforce what ruralguy mentioned, you probably don't want to be
storing that calculated value, just displaying it on the form.

Again, I'll recommend that you change the name of this control so that
there's no percent sign and no space.

To add an event procedure (instead of a macro), open the form in design
view. Click on the [Total Cost] control to highlight it. Select
Properties. Scroll down to the AfterUpdate event. Double-click on
"AfterUpdate" ... the words "Event Procedure" will fill in. Click on the
ellipses (...) to the right. This will take you into the VBA editor, where
you enter the code that sets the value to display in your "5%" text control.


Jeff Boyce
Microsoft Office/Access MVP

"Stefan" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...