Calculation In A Form For The New Guy

[Guest]

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.

 

[Jeff Boyce]

Stefan

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.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ruralguy via AccessMonster.com]

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
http://support.microsoft.com/?id=826763

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.

 

[Guest]

Jeff,

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.

-Stefan

Stefan

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.

Regards

Jeff Boyce
Microsoft Office/Access MVP

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.

 

[Jeff Boyce]

Stefan

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.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

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.

-Stefan

Stefan

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.

Regards

Jeff Boyce
Microsoft Office/Access MVP

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.