Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Dec 2014
    Posts
    386
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,301
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    544
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Office 2007

  4. #4
    Board Regular
    Join Date
    Dec 2014
    Posts
    386
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    544
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Office 2007

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •