A bit lost of making automatic calculations :-(

[evilcowstare via AccessMonster.com]

Hi, I have already tried looking at help and cant really find what I want to
do, or it could be that I just dont understand it.
I am trying to make 2 automatic calculations in my main form.

I have 4 fields...

Net Value called "netvalue"
Job Cost called "jobcost"
Margin called "margin"
and GP% called "GP"

The first 3 are all set up as currency with the default setting showing £0.00,
the GP is just a basic text box.
What I want it to do is that when the Net Value and Job Cost is entered it
automatically works out the Margin and GP for me and puts it in the relevant
box.

The calculation works like this...

For example

Net Value £500.00 - Job Cost £300.00 = £200 (this is the margin)
then
£200 (margin) / £500.00 (net value) = 0.4 (but needs to be shown as a
percentage of 40% in GP)

So in the end, the user has entered £500 into "netvalue" and £300 into
"jobcost and then the db has worked out that £200 goes into "margin and 40
goes into "GP"

Can anyone help me on how I need to do this or what the code is for it?

Thank You!!
Jay

 

[Jeff Boyce]

Jay

In the AfterUpdate event of each of the 'variable' controls on your form
(?[netvalue], [jobcost]?), you can add a bit of code that sets the values of
the other two controls.

That code would look something like (your syntax may vary...):

Me!Margin = Me!netvalue - Me!jobcost
Me!GP = (Me!netvalue - Me!jobcost)/Me!netvalue

By the way, how do you want to handle situations in which a user enters a
larger jobcost than netvalue? How about when one or both of those controls
is null? Or 0?

You'll need to decide how you want to handle those situations and modify
your code accordingly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[evilcowstare via AccessMonster.com]

Hi thanks

I think it might be better to have a lil button I think that people click
after they have added all the info, I cant seem to get the code right for the
on_click of the button.
I need it to do ...
netcost-jobcost and put the answer in margin
then straight away also do margin/netcost and put the answer as a percentage
in gp

Any ideas?

Thank You

This is the 1st time i have tried to do any sort of mathematical stuff in
access so its a bit new to me.

Jay

In the AfterUpdate event of each of the 'variable' controls on your form
(?[netvalue], [jobcost]?), you can add a bit of code that sets the values of
the other two controls.

That code would look something like (your syntax may vary...):

Me!Margin = Me!netvalue - Me!jobcost
Me!GP = (Me!netvalue - Me!jobcost)/Me!netvalue

By the way, how do you want to handle situations in which a user enters a
larger jobcost than netvalue? How about when one or both of those controls
is null? Or 0?

You'll need to decide how you want to handle those situations and modify
your code accordingly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have already tried looking at help and cant really find what I want
to

[quoted text clipped - 33 lines]

Thank You!!
Jay

 

[Jeff Boyce]

Jay

Whether you put the code in the AfterUpdate event of the two controls, or
put the code in the Click event of the command button, you'll be putting the
code...

From a user standpoint, having to click a button after I've already entered
the information is one step more than it should be. After all, if I did a
formula in Excel, I'd only need to enter the values to get the calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi thanks

I think it might be better to have a lil button I think that people click
after they have added all the info, I cant seem to get the code right for
the
on_click of the button.
I need it to do ...
netcost-jobcost and put the answer in margin
then straight away also do margin/netcost and put the answer as a
percentage
in gp

Any ideas?

Thank You

This is the 1st time i have tried to do any sort of mathematical stuff in
access so its a bit new to me.

Jay

In the AfterUpdate event of each of the 'variable' controls on your form
(?[netvalue], [jobcost]?), you can add a bit of code that sets the values
of
the other two controls.

That code would look something like (your syntax may vary...):

Me!Margin = Me!netvalue - Me!jobcost
Me!GP = (Me!netvalue - Me!jobcost)/Me!netvalue

By the way, how do you want to handle situations in which a user enters a
larger jobcost than netvalue? How about when one or both of those
controls
is null? Or 0?

You'll need to decide how you want to handle those situations and modify
your code accordingly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, I have already tried looking at help and cant really find what I
want
to

[quoted text clipped - 33 lines]

Thank You!!
Jay

 

[evilcowstare via AccessMonster.com]

But what is the code I need, I have tried the after update option doing...

margin=netvalue-jobcost
and it doesnt work??

Not sure what im doing wrong?

Jay

Whether you put the code in the AfterUpdate event of the two controls, or
put the code in the Click event of the command button, you'll be putting the
code...

From a user standpoint, having to click a button after I've already entered
the information is one step more than it should be. After all, if I did a
formula in Excel, I'd only need to enter the values to get the calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi thanks

[quoted text clipped - 46 lines]

 

[evilcowstare via AccessMonster.com]

PS
I know it works in Control Source, but I need the value to be stored in the
table. Anyway round it? as after update doesnt seem to do anything

Thanks

But what is the code I need, I have tried the after update option doing...

margin=netvalue-jobcost
and it doesnt work??

Not sure what im doing wrong?

Jay

[quoted text clipped - 16 lines]

 

[Jeff Boyce]

Jay

Hold on a second... You do NOT need to store the value in the table if you
have the [netvalue] and [jobcost]. In fact, storing calculated values like
that will require you to come up with a whole lot more code to make sure the
values stay synchronized. Instead of storing, use a query, form or report
to calculate the value of margin and GP on-the-fly.

You mention that the code doesn't work in the afterupdate event. By
"doesn't work", do you mean it doesn't calculated the margin or GP?
Calculates incorrectly? Gives an error message (please post it if so)?
Causes your PC to smoke? Knowing a bit more about what you mean by "doesn't
work" might help the newgroup readers figure out what's happening.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

PS
I know it works in Control Source, but I need the value to be stored in
the
table. Anyway round it? as after update doesnt seem to do anything

Thanks

But what is the code I need, I have tried the after update option doing...

margin=netvalue-jobcost
and it doesnt work??

Not sure what im doing wrong?

Jay

[quoted text clipped - 16 lines]

Thank You!!
Jay

 

[evilcowstare via AccessMonster.com]

Hi sorry Im still new to all this stuff.
I really need the values stored or there is no point having them, if I cant
then ill just let them do the calculations themselves.
I had no problem doing the calcualtions with the control source but as you
say, they then cant be stored.
When I did the afterupdate, i didnt do anything, no errors, no nothing, made
no difference. It doesnt matter if it comes to it ill add a couple of
seperate info boxes that work out the calculation then they can just copy it
by hand into the txt box for storing. Unless you know of a way I could get it
to copy the answer from one textbox into another without using control source?


Thanks for your help, sorry if im a bit confusing!

Jay

Hold on a second... You do NOT need to store the value in the table if you
have the [netvalue] and [jobcost]. In fact, storing calculated values like
that will require you to come up with a whole lot more code to make sure the
values stay synchronized. Instead of storing, use a query, form or report
to calculate the value of margin and GP on-the-fly.

You mention that the code doesn't work in the afterupdate event. By
"doesn't work", do you mean it doesn't calculated the margin or GP?
Calculates incorrectly? Gives an error message (please post it if so)?
Causes your PC to smoke? Knowing a bit more about what you mean by "doesn't
work" might help the newgroup readers figure out what's happening.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

PS
I know it works in Control Source, but I need the value to be stored in

[quoted text clipped - 15 lines]

 

[John W. Vinson]

I really need the values stored or there is no point having them,

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

WHY do you insist that you must store this value, given that it can be
derived, over and over, whenever you need it? If you're assuming that it must
be stored in a Table to be reported, searched, or sorted, revise your
assumption, because it is incorrect!

John W. Vinson [MVP]

 

[evilcowstare via AccessMonster.com]

Hi, ahh ok guess I should leave it alone then. The main reason I wanted it in
a table was so that it would be stored against the record but I suppose
realistically if the information is only ever viewed in a form or report then
I guess it doesnt really matter. Hadn't really thought the whole thing
through and didnt realise it could cause such problems by storing it. Thanks
for your help, it makes sense now I didnt really think about the report still
working off the info unless it was stored but now I know it will be easy.
Thanks

 

[Jeff Boyce]

Jay

I (hopefully) didn't say you "can't", but that doing so is rarely necessary
or a good idea.

My suggestions about using the AfterUpdate event did not involve
ControlSource. I'm not sure where/how you are involving it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi sorry Im still new to all this stuff.
I really need the values stored or there is no point having them, if I
cant
then ill just let them do the calculations themselves.
I had no problem doing the calcualtions with the control source but as you
say, they then cant be stored.
When I did the afterupdate, i didnt do anything, no errors, no nothing,
made
no difference. It doesnt matter if it comes to it ill add a couple of
seperate info boxes that work out the calculation then they can just copy
it
by hand into the txt box for storing. Unless you know of a way I could get
it
to copy the answer from one textbox into another without using control
source?


Thanks for your help, sorry if im a bit confusing!

Jay

Hold on a second... You do NOT need to store the value in the table if
you
have the [netvalue] and [jobcost]. In fact, storing calculated values
like
that will require you to come up with a whole lot more code to make sure
the
values stay synchronized. Instead of storing, use a query, form or report
to calculate the value of margin and GP on-the-fly.

You mention that the code doesn't work in the afterupdate event. By
"doesn't work", do you mean it doesn't calculated the margin or GP?
Calculates incorrectly? Gives an error message (please post it if so)?
Causes your PC to smoke? Knowing a bit more about what you mean by
"doesn't
work" might help the newgroup readers figure out what's happening.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

PS
I know it works in Control Source, but I need the value to be stored in

[quoted text clipped - 15 lines]

Thank You!!
Jay