1. Registered User
Join Date
Nov 2011
Posts
2

Hi,

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.

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

James

2. Registered User
Join Date
Jan 2012
Posts
98
James,

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.

Me.nameofcontrol.Requery

Hope this helps,
Dave

3. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
You probably need to take into account the fact that one or more of these items may be Null (empty) so

Code:
`= [nameofcontrolwithsubtotal1] + [nameofcontrolwithsubtotal2]`
probably should be
Code:
`= 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:

OrdersTable
OrdersID 'Primary Key
CustomerName
...and so forth

ProductsTable
OrdersID 'Foreign Key
ProductsID 'Primary Key
ProductName
ProductPrice
...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.