Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011

    Exclamation Unanswered: Calculating in Forms


    I am currently developing a database that creates an order for a customer. Within this database I have a form into which the idea is to type the product ordered, the price and the quantity. I then want the form to do the calculation of Price * Quantity. I have 8 separate boxes for Product Ordered, another 8 for Price and another 8 for Quantity. So I need to be able to do 8 Calculations in total for this section.

    The next thing that I need to do is to be able to then add all the totals from the above calculations and have this put into another text box.

    I currently have some code in place that works to do the first 8 total calculations for Price * Quantity however cannot figure out at all how to get these totals to add together to produce a totally separate total.

    If any body needs any more information I will gladly give them what ever they need!

    Thank you so much in advance. This is kind of urgent!


  2. #2
    Join Date
    Jan 2012

    As you have already been able to do price * QTY I am assuming you have a text box with its control source as something like:

    = [nameofcontrolwithprice] * [nameofcontrolwithqty]

    Let's say this sub total is displayed in a text box and is called txt1 and you have the exact same set up for a second product with the second sub total displayed in a text box called txt2.

    The overall total would simply be:

    = [nameofcontrolwithsubtotal1] + [nameofcontrolwithsubtotal2]

    It is worth mentioning that on the AfterUpdate event on each price and QTY controls you should run code that requeries the sub total.


    Hope this helps,

  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    You probably need to take into account the fact that one or more of these items may be Null (empty) so

    = [nameofcontrolwithsubtotal1] + [nameofcontrolwithsubtotal2]
    probably should be
    = Nz([nameofcontrolwithsubtotal1], 0) + Nz([nameofcontrolwithsubtotal2], 0)
    Having said that, I have to tell you that this is an unusual way of handling this type of situation. The standard way of doing an order/invoice situation would be using a one-to-many scenario, with the 'one' side being the Orders and the 'many' side being the Products. This is usually handled with a Main Form/Subform set up.

    In general terms the Main Form would be a Single View Form and based on a OrdersTable. The Subform would probably be a Datasheet View Form (to better display the 'Products' Field) based on a Products Table.

    The Fields would typically be something like this:

    OrdersID 'Primary Key
    ...and so forth

    OrdersID 'Foreign Key
    ProductsID 'Primary Key
    ...and so forth

    Once you've designed your OrdersForm and ProductsForm, based on OrdersTable and ProductsTable, respectively, open the Main Form (OrdersForm) in Form Design View and add a Subform Control to it. The Subform Wizard will walk you through the process. When asked, base your Subform on the ProductsForm. Seeing that both Tables the Forms are based on have a Field named OrdersID, the Wizard should ask if you want to link the Main Form /Subform using these Fields. Answer OK and Bob's your uncle!

    This would allow for an Order with one Product or one with a gazillion Products. You'd place a Calculated Control on your Subform to add up the Product * Price column then have a Control on the Main Form with this Control as its Control Source.

    Linq ;0>
    Last edited by Missinglinq; 01-10-12 at 21:54.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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