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

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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.
 
Upvote 0
but i need all 4 values stored.

i solved it with this solution:
Code:
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top