Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Location
    Devon, England
    Posts
    8

    Unanswered: Stuck on a calculation in a query

    Hi everyone

    I'm building a database to process orders. I'm getting there (slowly) and have got a form, with a sub-form, set up that allows me to create a new order record for a client and add a number of products to it. The form is based on a query that i've set up.

    Within the query, i've got some calculations to work out the cost of x quantity of a product, the VAT, a discount (if selected) and the total. All good so far.

    The thing is, it's supposed to allow as many products to be added to one order as required, in any quantity. So, if i've order 5 of one thing, 4 of another i want it to not only tell me the total for each line but the total cost overall.

    I've attached two screen prints: one showing the order screen as it is so far so you can see what i'm on about and another showing the calculation bit of the query i mentioned.

    My question is: how can i place a label/text box on the main part of the form and have the totals of each product line be added-up and populate the label on the form?

    TIA, Jamie
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well my neck is sore now ^^

    Add them up first in your subform's form footer in a control called something like txtSalesTotal. Then, on your mainform you can make a direct reference to the txtSalesTotal control on the subform.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2009
    Location
    Devon, England
    Posts
    8
    Ok, sounds good. But, i'm not sure how to do that. Would you be able to explain the process for me?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bah, I've had to type this twice now. Damned computers that lock up / freeze are the pits.

    Anyway, I can only give you general steps:

    Create a textbox in your subform's footer called txtTotal.
    Set the control source for txtTotal to =Sum([FieldToAddUp]).
    Set the form footer's Visible property to No.
    Create a textbox in your mainform called txtSubformTotal.
    Set it's control source to refer to the txtTotal field on the subform. The syntax for that might be something like [subformObjectName]![txtTotal], but I am not so sure on that; I'd have to check it out with the build button.

    Hopefully that's enough for you to get started
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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