Calculating with forms...please help!! (1 Viewer)

[The_Freak]

Hello,

question one:
==============

I have a table with the following fields
all of the type currency

A
B
C

Now i made a form based on the same table
form looks also like

A
B
C

now i want that in field C the result will be recorded of the expression A-B

So the formula i want to use is C=A-B

On the formfield C i made the expression =[A]-

Ok now when i put values into A and B he calculates C offcourse but i want the form to store that result in the table as well!

but now it only displays C on the form but i don't record the result of the calculation in the table!

Anybody know how i can make it that it saves C in the table??

Question two:
===============

When A-B is smaller then 0 then the result of C should be 0. so NO negative numbers...but it don't work now...when i put in par example in A 5 and in B 7 then i see in the formfield C the result -2 and that should be 0.

Anybody know how to solve both or just one problem with visual basic or something else???

thanks in regards,

FREAK

 

[DES]

The first question becomes why do you want to save in a table a value which you can always recalculate later. Most people will insist this is a no no.

However to solve the problem you need to have a hidden text box whose data source is C from your table.

The text box that was C on your form should be an unbound text box.

In the Forms On Current event you need a procedure which will make the hidden text box C = the unbound text box. So as you move from record to record the value in the table will be added. You could also make a button to do the same process if your form does not allow the user to move to the next record.

As for the equation you need to be using an IIF statement.

=IIF([A]-<0,0,[A]-)

 

[The_Freak]

"The first question becomes why do you want to save in a table a value which you can always recalculate later. Most people will insist this is a no no."

- Well i want to make search functions in my database and i want the search function (query) to display field C aswell....hope you understand now why i want to save it

However to solve the problem you need to have a hidden text box whose data source is C from your table.

The text box that was C on your form should be an unbound text box.

"In the Forms On Current event you need a procedure which will make the hidden text box C = the unbound text box. So as you move from record to record the value in the table will be added. You could also make a button to do the same process if your form does not allow the user to move to the next record."

- Don't know exactly what you mean here...the users can go to the next record...but do you know the procedure how to let it save it???

"As for the equation you need to be using an IIF statement.

=IIF([A]-<0,0,[A]-)"

- THANK YOU VERY MUCH!!!!!!! this works!

but it should be =IIF([A]-<0;0;[A]-) instead of =IIF([A]-<0,0,[A]-)

but thanks anyway......

anybody know how to solve my first question?

 

[DES]

Hmmmm. Lets try this again. I can understand you wanting to return the value of C later on in another form or report but since you will always have the values of A and B then you can always get the value of C.

By saving the value C in your table you will be needlessly increasing the amount of information being saved and thus increase the size of the database. But again that is your choice.

As for the IIF statement the separators may be different in 2000 but in 97 they ARE commas

As for the part you did not understand lets see if I can explain it a different way.

If the text box that you now have on your form has for its data source an equation then it can not be a bound text box. To get the value of C into the table the easiest way is to hid a bound text box on the form bound to the C in the table.

At some point you will need to write code to have the value of the bound text box = the value of the unbound text box thus giving you the value in your table.

The only trick now become where to put the code to have it do it.

As suggested earlier you could put it on a command button. However upon more thought it would probably make more sense to put it on the After Update of the text box for your B value.

So assuming that your text boxes are named as follows:
A = [A]
B =
The visible C = [MyC] Because this is the unbound text box.
The hidden text box C = [C]

Then this is the code.

Private Sub B_AfterUpdate()
[C] = [MyC]
End Sub

Once you save the record or move to a new record then the value if MyC will be saved to your table.


[This message has been edited by DES (edited 06-01-2001).]

 

[The_Freak]

Thanks for your help DES

I don't know why but the seperator in the IFF statement in my Access 97 is ; and not , as you said...don't know why this is....you might have the developer edition or something....

but it dont matter now because its solved

My first question i solved on a different way.....

I made an updatequery which calculates C in the query(not table)and then synchronise the C in the query with the C in the table when the querry is runned.

normally you get 2 warning messages after before Access is running your query....you can put those off by let the query run through a macro with the properties

SetWarning = NO
RunQuery = Query1

But thanks again for ur help!

Offcourse saving C into the table will increase the size of the database but its an application i'm making for a school project so who cares anyway