How to calculate textbox values and store all data in my table?

[behedwin]

Hey!

I have a form that looks like this:

TEXTBOX1 | TEXTBOX2 | TEXTBOX3 | TEXTBOX4

Each textbox is bound to a table column in a table called: Volume_tbl

I want to store all inputs made by the user.
But now i want to add calculations.... dont know how tho.

I want to take TEXTBOX1 + TEXTBOX2 - TEXTBOX3 = TEXTBOX4

Textbox4 is not an input field for the user, it is disabled. But i want the calculated result from the users input in textbox 1-3 to be saved also.

 

[xenou]

In the FORM before update event I guess you can just do the needful - set the values of textbox 4 to the added values of the first three. Or if the field is hidden (or even if it isn't) you can add the code to update the values anytime one of the other three textboxes is changed (using their CONTROL after update events). Watch out for nulls and handle that properly. Also consider not saving the value since it can be calculated whenever needed in your queries/views/reports so no real need to save this.

 

[welshgasman]

Firstly any time you have Field1, Field2 that generally highlights the fact that your database structure is incorrect. You are thinking in Excel mode, not Access mode.
Secondly you should only store 1 to 3 and calculate the 4th when needed. So on your form that would be an unbound control.

 

[behedwin]

but i need all 4 values stored.

i solved it with this solution:

Form_Rapport_frm.Text4 = Form_Rapport_frm.Text1 + Form_Rapport_frm.Text2 - Form_Rapport_frm.Text3

It works great as far as i know.

But do you recomend me instead just storing the TEXTBOX 1-3 values.
Then when i need the calculation i do above dont store that value atall?

 

[welshgasman]

It is generally recommended not to store calculated values, but calculate when needed. That way if any of the underlying fields change, no need to make sure a recalc has occurred and nothing gets out of sync.
If fields 1-3 are only ever updated via that form, then that should not be too much of a problem though.
Databases grow in rows not columns, so really you would have a table

ID
KeyText key to Textfield
KeyIndex 1, 2 ,3......9999
KeyValue what you hold now

Then you would add all the KeyValues for for a particular Keytext

That way if you suddenly need Text4 and Text5 will hold the totals, it is very easy to implement.

With your approach you would need to amend forms and code behind them.

Only you know if that might happen.