Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Location
    Ireland
    Posts
    4

    Unanswered: Simple Calculation???

    Hello All, I am a mid range user of Microsoft Access (2000) and am currently putting together a quote based database to try and make my life easier at work. However, I have come across an issue that has stumped me and I am sure that the solution is very simple if you are a heavy user. Basically I three fields [item_cost] [item_num] and [total_cost].

    Now the first two fields are manual entry but I would like the last one to automatically calculate the result and then store it in the table. I can make the calulation or store the sum after doing the calculation manually but cannot do both

    Any help is appreciated

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Don't store calculations. They can be done any time, automatically, based on the values of the relevant fields. If you are creating an Item table, total_cost should not be a field.

  3. #3
    Join Date
    Jan 2005
    Location
    Ireland
    Posts
    4
    Yes I understand the problems associated with this, however, this is a subform in a main form where I add items to a quote list. I can calculate the total cost per line item but cannot get a total of all the lines. Here is what I have done

    In the footer of the sub form I have a small calculation =Sum(([item_cost]*[item_num])) and named the text box [subtotal], now this gives me the total value for the order but because it is in the footer it does not appear in the main form. So I created another text box in the main form and name it [total] the formula in this one was a simple =[Forms]![order_subform]![subtotal]

    Alas I get a NAME? error when I preview it

    What am I doing wrong?

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    I am not an expert.

    Hello Nibbler and welcome to the board.
    Please note I am not an expert of any sort, that said:
    It sounds like you have textbox1 and textbox 2 that you enter values for and textbox3 is something like =[textbox1]*[textbox2] and when you exit textbox2 3 is updated. Correct ?
    If so why not add soem code to the After Update event for textbox2.
    Sample:
    Code:
    Private Sub Textbox2_AfterUpdate()
    Dim Sql as String 
    Sql = "INSERT INTO tblMyTable ( field ) SELECT [forms]![MyFrom]![textbox3] "
    Docmd.Setwarnings False ' this is a personal preference of mine
    DoCmd.RunSQL Sql
    DoCmd.setwarnings True ' turn those warnings back on.
    End Sub
    That is my 2 cents worth.
    Darasen

  5. #5
    Join Date
    Jan 2005
    Location
    Ireland
    Posts
    4
    Hi Darsen,

    Thanks for the tip, I am not too well up on VB and am not sure where you want me to put in my form names and what are commands but I reckon it should read like this, correct?

    Private Sub order_num_AfterUpdate()
    Dim Sql as String
    Sql = "INSERT INTO tblMyTable ( field ) SELECT [forms]![order_detail]![total] "
    Docmd.Setwarnings False ' this is a personal preference of mine
    DoCmd.RunSQL Sql
    DoCmd.setwarnings True ' turn those warnings back on.
    End Sub

    Thanks
    Nibbler

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use the DSUM(expr, domain, criteria) function in your outer form to calculate the total of related records in the subtable. You may need to issue a recalc or refresh order when control is returned to the main form from the subform...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Location
    Ireland
    Posts
    4
    Hi Blindman, thanks for the tip - do you have an example expression that I can use?

    Thanks

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That was a sample expression...

    Maybe you should check the help file for more information?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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