Calculation/Formula in Forms?

[susan]

I have just created a form and need to put 2 calculation fields using input
from the form...

Fields on forms (to be filled out by users):
Current Price
New Price
12 Month Volume

I want to access to automatically calculate when data is entered:
1. 12 Month Spend = New Price * 12 Month Volume
2. 12 Month FCST = (Current Price - New Price) * 12 Month Volume

I also don't want the "box" to appear as if the user can input a number in
there.

Can anyone help?

Thanks,
Susan

 

[banem2]

I have just created a form and need to put 2 calculation fields using input
from the form...

Fields on forms (to be filled out by users):
Current Price
New Price
12 Month Volume

I want to access to automatically calculate when data is entered:
1.  12 Month Spend = New Price * 12 Month Volume
2.  12 Month FCST = (Current Price - New Price) * 12 Month Volume

I also don't want the "box" to appear as if the user can input a number in
there.

Can anyone help?

Thanks,
Susan

On top of previous advice, keep in mind you need to use brackets
around field names as you have space in name, like:

[12 Month Spend] = [New Price] * [12 Month Volume]
[12 Month FCST] = ([Current Price] - [New Price]) * [12 Month Volume]

You could run this calc when each of the fields update (AfterUpdate
event), but when any of the fields in equation are empty, it will
produce Error instead 0. You can add NZ() function to convert fields
with no entry to zero, like:

[12 Month Spend] = NZ([New Price], 0) * NZ([12 Month Volume], 0)
etc.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[susan]

thanks for the solution - could you help with writing the code? i am new to
access and have no idea how to put together a calculation in the "after
update" event...
thanks again!

I have just created a form and need to put 2 calculation fields using input
from the form...

Fields on forms (to be filled out by users):
Current Price
New Price
12 Month Volume

I want to access to automatically calculate when data is entered:
1. 12 Month Spend = New Price * 12 Month Volume
2. 12 Month FCST = (Current Price - New Price) * 12 Month Volume

I also don't want the "box" to appear as if the user can input a number in
there.

Can anyone help?

Thanks,
Susan

On top of previous advice, keep in mind you need to use brackets
around field names as you have space in name, like:

[12 Month Spend] = [New Price] * [12 Month Volume]
[12 Month FCST] = ([Current Price] - [New Price]) * [12 Month Volume]

You could run this calc when each of the fields update (AfterUpdate
event), but when any of the fields in equation are empty, it will
produce Error instead 0. You can add NZ() function to convert fields
with no entry to zero, like:

[12 Month Spend] = NZ([New Price], 0) * NZ([12 Month Volume], 0)
etc.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[susan]

something is wrong with my computer/access...i couldn't open the visual basic
module (it was open just an hour ago!) and i am stuck and can't do anything
about it!
i am using access 2003 - i thought there will be more to the code than what
branislav showed me? yes? no?

thanks!

Start by bringing up the property sheet for each control and select the
Events tab and press the "..." button in the AfterUpdate row and select code.
Then when you get to the code module just put in what Branislav Mihaljev
showed you. What version of Access are you using?

thanks for the solution - could you help with writing the code? i am new to
access and have no idea how to put together a calculation in the "after
update" event...
thanks again!

I have just created a form and need to put 2 calculation fields using input
from the form...

[quoted text clipped - 33 lines]

Branislav Mihaljev
Microsoft Access MVP

 

[susan]

yes i have compact & repair a few times already...reboot the computer...still
not working. ok, i will import my db to a new db and try the code. i will
let you know if it works!!!
thankyou so much for your help!!!!

Have you done a Compact and Repair on your db lately? Tools>Database
Utilities>...
It may be necessary to import your db into a fresh db to eliminate any
problems.
Putting:
[12 Month Spend] = [New Price] * [12 Month Volume]
[12 Month FCST] = ([Current Price] - [New Price]) * [12 Month Volume]
in the AfterUpdate event of your controls should work assuming the names of
the controls are correct and all of the controls have values.

something is wrong with my computer/access...i couldn't open the visual basic
module (it was open just an hour ago!) and i am stuck and can't do anything
about it!
i am using access 2003 - i thought there will be more to the code than what
branislav showed me? yes? no?

thanks!

Start by bringing up the property sheet for each control and select the
Events tab and press the "..." button in the AfterUpdate row and select code.

[quoted text clipped - 11 lines]

Branislav Mihaljev
Microsoft Access MVP

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[susan]

ok so i imported the old db to a new db and the vb module works...but the
codes are not working...here's what i did:

for the 2 fields i want to have calculations, i went into properties-->
"event" --> "after update" -->[event procedure]--> "..." and put in the
following code:

Option Compare Database

Private Sub Ctl12_Month_FCST_MPV_AfterUpdate()
[12 Month FCST MPV] = Nz([Current Price] - [New Price], 0) * Nz([12 Month
FCST Volume], 0)

End Sub

Private Sub Ctl12_Month_Spend_AfterUpdate()
[12 Month Spend] = Nz([New Price], 0) * Nz([12 Month FCST Volume], 0)


End Sub

--------------
I typed them in in seperate properties windows but both are now showing up
together.

what's not working is, i tried to input data into the new price/current
price/volume but nothing is being calculated in the 2 fields...what did i do
wrong???

thanks again!!!
susan

yes i have compact & repair a few times already...reboot the computer...still
not working. ok, i will import my db to a new db and try the code. i will
let you know if it works!!!
thankyou so much for your help!!!!

Have you done a Compact and Repair on your db lately? Tools>Database
Utilities>...
It may be necessary to import your db into a fresh db to eliminate any
problems.
Putting:
[12 Month Spend] = [New Price] * [12 Month Volume]
[12 Month FCST] = ([Current Price] - [New Price]) * [12 Month Volume]
in the AfterUpdate event of your controls should work assuming the names of
the controls are correct and all of the controls have values.

something is wrong with my computer/access...i couldn't open the visual basic
module (it was open just an hour ago!) and i am stuck and can't do anything
about it!
i am using access 2003 - i thought there will be more to the code than what
branislav showed me? yes? no?

thanks!

Start by bringing up the property sheet for each control and select the
Events tab and press the "..." button in the AfterUpdate row and select code.
[quoted text clipped - 11 lines]
Branislav Mihaljev
Microsoft Access MVP

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Beetle]

Although I agree with ruralguy that you should avoid using spaces in field
and control names, I don't think that's causing your current problem. You
put your code in the After Update event of the calculated controls on your
form, which is wrong. It needs to go in the After Update event of the controls
that hold the stored values. It would also be a good idea to put the in
the Current event of the form itself. As an example;

***************************

Private Sub New_Price_AfterUpdate()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
***************************

Private Sub Current_Price_AfterUpdate()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
***************************

Private Sub 12_Month_Volume_AfterUpdate()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
***************************

Private Sub Form_Current()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
****************************


The Me! reference is a way of making sure Access knows that the controls
you are referencing are on the form you're currently coding for.

--
_________

Sean Bailey

ok so i imported the old db to a new db and the vb module works...but the
codes are not working...here's what i did:

for the 2 fields i want to have calculations, i went into properties-->
"event" --> "after update" -->[event procedure]--> "..." and put in the
following code:

Option Compare Database

Private Sub Ctl12_Month_FCST_MPV_AfterUpdate()
[12 Month FCST MPV] = Nz([Current Price] - [New Price], 0) * Nz([12 Month
FCST Volume], 0)

End Sub

Private Sub Ctl12_Month_Spend_AfterUpdate()
[12 Month Spend] = Nz([New Price], 0) * Nz([12 Month FCST Volume], 0)


End Sub

--------------
I typed them in in seperate properties windows but both are now showing up
together.

what's not working is, i tried to input data into the new price/current
price/volume but nothing is being calculated in the 2 fields...what did i do
wrong???

thanks again!!!
susan

yes i have compact & repair a few times already...reboot the computer...still
not working. ok, i will import my db to a new db and try the code. i will
let you know if it works!!!
thankyou so much for your help!!!!

Have you done a Compact and Repair on your db lately? Tools>Database
Utilities>...
It may be necessary to import your db into a fresh db to eliminate any
problems.
Putting:
[12 Month Spend] = [New Price] * [12 Month Volume]
[12 Month FCST] = ([Current Price] - [New Price]) * [12 Month Volume]
in the AfterUpdate event of your controls should work assuming the names of
the controls are correct and all of the controls have values.


susan wrote:
something is wrong with my computer/access...i couldn't open the visual basic
module (it was open just an hour ago!) and i am stuck and can't do anything
about it!
i am using access 2003 - i thought there will be more to the code than what
branislav showed me? yes? no?

thanks!

Start by bringing up the property sheet for each control and select the
Events tab and press the "..." button in the AfterUpdate row and select code.
[quoted text clipped - 11 lines]
Branislav Mihaljev
Microsoft Access MVP

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[susan]

thanks beetle - i will try your suggestion...tho i am new to access and i
will need some time to digest ur code/what you have just said! let's hope it
works!
thanks again beetle and ruralguy!

Although I agree with ruralguy that you should avoid using spaces in field
and control names, I don't think that's causing your current problem. You
put your code in the After Update event of the calculated controls on your
form, which is wrong. It needs to go in the After Update event of the controls
that hold the stored values. It would also be a good idea to put the in
the Current event of the form itself. As an example;

***************************

Private Sub New_Price_AfterUpdate()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
***************************

Private Sub Current_Price_AfterUpdate()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
***************************

Private Sub 12_Month_Volume_AfterUpdate()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
***************************

Private Sub Form_Current()

Me![12 Month Spend] = Me![New Price] * Me![12 Month Volume]
Me![12 Month FCST] = (Me![Current Price] - Me![New Price]) * Me![12 Month
Volume]

End Sub
****************************


The Me! reference is a way of making sure Access knows that the controls
you are referencing are on the form you're currently coding for.

--
_________

Sean Bailey

ok so i imported the old db to a new db and the vb module works...but the
codes are not working...here's what i did:

for the 2 fields i want to have calculations, i went into properties-->
"event" --> "after update" -->[event procedure]--> "..." and put in the
following code:

Option Compare Database

Private Sub Ctl12_Month_FCST_MPV_AfterUpdate()
[12 Month FCST MPV] = Nz([Current Price] - [New Price], 0) * Nz([12 Month
FCST Volume], 0)

End Sub

Private Sub Ctl12_Month_Spend_AfterUpdate()
[12 Month Spend] = Nz([New Price], 0) * Nz([12 Month FCST Volume], 0)


End Sub

--------------
I typed them in in seperate properties windows but both are now showing up
together.

what's not working is, i tried to input data into the new price/current
price/volume but nothing is being calculated in the 2 fields...what did i do
wrong???

thanks again!!!
susan

yes i have compact & repair a few times already...reboot the computer...still
not working. ok, i will import my db to a new db and try the code. i will
let you know if it works!!!
thankyou so much for your help!!!!

:

Have you done a Compact and Repair on your db lately? Tools>Database
Utilities>...
It may be necessary to import your db into a fresh db to eliminate any
problems.
Putting:
[12 Month Spend] = [New Price] * [12 Month Volume]
[12 Month FCST] = ([Current Price] - [New Price]) * [12 Month Volume]
in the AfterUpdate event of your controls should work assuming the names of
the controls are correct and all of the controls have values.


susan wrote:
something is wrong with my computer/access...i couldn't open the visual basic
module (it was open just an hour ago!) and i am stuck and can't do anything
about it!
i am using access 2003 - i thought there will be more to the code than what
branislav showed me? yes? no?

thanks!

Start by bringing up the property sheet for each control and select the
Events tab and press the "..." button in the AfterUpdate row and select code.
[quoted text clipped - 11 lines]
Branislav Mihaljev
Microsoft Access MVP

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[susan]

Beetle & Ruralguy:
IT WORKED!!!! THANKU THANKU THANKU!!!
I was afraid to change the field names around (to take out the space) but it
works with the spaces...I did have to rename the "current price" because of
the code: private sub form_current()
Thank you for helping me!!!
Susan

Good luck Susan. Feel free to post back if you get stuck again. There is
lots of assistance here.

thanks beetle - i will try your suggestion...tho i am new to access and i
will need some time to digest ur code/what you have just said! let's hope it
works!
thanks again beetle and ruralguy!

Although I agree with ruralguy that you should avoid using spaces in field
and control names, I don't think that's causing your current problem. You

[quoted text clipped - 103 lines]

Branislav Mihaljev
Microsoft Access MVP

 

[susan]

Branislav, Bettle, ruralguy:
THANKS for your help!!!
Susan

 

[susan]

i actually have another problem with my project and i posted another question
today titled "form/subform"...think u can help? thanks!