Command Button

[Sierra Mike]

Hi all,

I am new to the forum so plz forgive me for any mistakes.
Basically I am interested in creating a form in access which has a button called CALCULATE. once i have entered the required data in the form i would like to click the button and the calculated result should be displayed in the field bellow the button.

Eg: Suppose X and Y are two numbers and i want to find there sum by clicking on the CALCULATE button and the result should be displayed in front of Z.



X : ______

Y: _______

CALCULATE

Z :______


I am able to write the code for the calculation in VB but the result is not being displayed in the required field. Can someone plz help me.

[June7]

Why do you need a CALCULATE button? Why not just an expression in textbox Z ControlSource?

[Sierra Mike]

Thanks for the reply........I agree with your comment but my problem is not that simple. the calculation to be perform is not simple addition. it involves sigma function and probability hence i need to write a code in VBA. So i thought of adding a button and write the code. but now the result is not displaying in the required field space. Plz help

[trevor40]

create 3 fields on the form, input_a, input_b, result, or whaterver
then create your button, in the onclick event put your code to sum, add or whaterver

me.result = ... your sigma function goes here ...
EG
me.result = me.input_a + me.input_b
(not into sigma functions, the rest is up to you)

[Sierra Mike]

hi all,

thanks for all the help but my problem is still not solved. the above code does not display the result in the form......i want that after clicking on the button the result should be displayed on the result field in the form.

[June7]

Are form and textboxes bound?

[trevor40]

Ok, ensure that the 3 fields on the form have their format set to number type,
Me.result = Me.Text1 + Me.Text2 (all set to general number) works ok
you can also use vba breakpoints to determine were a problem may be...

[Missinglinq]

At this point you probably need to show us the exact code you've tried that doesn't work.

You also need to be aware that if any of your Controls are empty (i.e. Null) then adding them together will result in a Null!

If, for instance,

Me.X = 5

and

Me.Y = 10

then

Me.X + Me.Y = 15

But if

Me.X = 5

and

Me.Y is empty (Null)

then

Me.X + Me.Y will be Null (appear 'empty')

Depending on your exact code, this may be what is happening. To keep this from happening, you'd have to use the Nz() function. This function assigns a Value to a Control that is empty (Null). For instance, with

Nz(Me.Y, 0)

you're telling Access that if Me.Y is empty, or Null, then assign the Value of 0 (zero) to it. So, as said before, if

Me.X = 5

and

Me.Y is empty (Null)

then

Me.X + Me.Y will be Null (appear 'empty')

But if you used

Nz(Me.X, 0) + Nz(Me.Y, 0)

it would be processed as

5 + 0

and so

Nz(Me.X, 0) + Nz(Me.Y, 0) = 5

The 0 in

Nz(Me.X, 0)

could be replaced with any other appropriate Value. If, for instance, the Control named X could possibly be used as a divisor, using 0 (zero) wouldn't work. While most people would consider that dividing a number by nothing would be equal to that number, division by zero is illegal in VBA, and pops an error.
So, if

Me.A = 10

and

Me.B is empty (Null)

then

Me.A/Me.B will pop an error!

But

Me.A/Nz(Me.B,1) will, in fact, equal 10, because you're now dividing 10 by 1, which equals 10.

You can even assign a Text Value, if the Control is bound to a Text Field.

If you simply use

Nz(Me.ControlName)

without specifying a Value to insert, Access assigns a Value appropriate for the DataType of the Field that is Bound to ControlName.

Linq ;0)>

[June7]

In brief, arithmetic with Null results in Null.

[Missinglinq]

Well said!

[Sierra Mike]

thanks for the replies.........But the problem still remains

What i am Doing is.........I make a fresh table named Sum. this table has three Fields Namely A, B, Sum all have data type as number (general).
Now i go ahead and use the form wizard to make the form named Sum. I take all the fields (i.e. A,B, Sum). Now I add a button named Calculate.
I want the sum of A and B should be displayed once i click the button Calculate. So i write the code in VBA i.e.

Dim A,B, Sum as Integer
Sum = A + B

But the result does not show in the Sum field in the form.
I guess i am going wrong in some settings in access.............can anyone help.

[June7]

The variables are not needed. The code needs to refer to the fields or textboxes, not declared variables.

Me.Sum = Me.A + Me.B

[trevor40]

I hope I did this correct, If so there is a zip file for you. Very basic but does what you describe above.

test1.zip


sum is an access word leave it alone!, this may be the cause of the issue.
use sum1 in stead

[trevor40]

ps I also misread your post
the code - Sum = A + B only sums the two variables a and b

you need to reference the form fields a and b

me.a
me.b

OR

Forms![Your form name].a
Forms![Your form name].b

[Sierra Mike]

thanks alot to all of you one of my problems is solved.............it was a great learning experience.