Thanks very much for both your comments, Daryl and Bruce,
Daryl - I did get the subtotals to work in the header and footer of both,
thank you (one wasn't!)
__________________________________________________________
So - This is what I am trying to achieve:
In our business, our orders are made up of products (pizzas actually!) to
which the customer can add or remove toppings. Adding a topping costs $1.00.
My AddAnOrderandDetailsform captures the customer.
My OrderDetailsSubform captures the products (S Margherita, for example),
the unit price, quantity, and extended price (yes calculated by multiplying
Quantity ad UnitPrice).
I then have my ItemDetailsSubform which I want to capture the additional
toppings or toppings removed, eg + Pepperoni or - Pepperoni.
Both my OrderDetailsSubform and ItemDetailsSubform are datasheet view.
The prices load automatically using an afterupdate code on the cboProductID
on each and the txtOrderDetailsTotal and txtItemDetailsTotal calculate, as
mentioned.
__________________________________________________________________
1. Ideally I would like the cost of the additional toppings to be added to
the extendedprice of the item on the OrderDetailssubform and hence the
GrandTotal on the AddAnOrderandDetailsform or, if this is too tricky, just to
the GrandTotal on the AddAnOrderandDetailsform. Either way this cost needs to
be included in the final price.
2. I’m not sure what you mean by:
“Wouldn't the item details already be summed to the order
details?â€
I think that’s what I’m trying to do by having the extra cost ($1.00) for
the topping selected in the ItemDetailsSubform added to the extendedprice on
the OrderDetailsSubform……… maybe….?
3. I would like the ItemDetailsSubform to load when I change a combo box on
the OrderDetailsSubform (cboplus/minus) to either + or -. At the moment it
loads but the record won't save as the OrderDetails record hasn't saved yet,
therefore it has no corresponding record. I have managed to get it to link to
the OrderDetails record via the OrderID but how do I get this to save at the
same time as loading the ItemDetails subform?
4. Besides all this I have read that you shouldn’t ‘save’ the price of
orders, that they should only ever be calculated controls. I’m not sure I
understand this correctly. If the price is always calculated what happens if
the price of a product changes, and I go in to the change that price,
wouldn’t all the past orders with that product change? I don’t want this as I
want to be able to look back and compare etc.
I hope I haven’t bombarded you both with too much information and questions!!!
I really appreciate you’re help and time.
Thanks,
Rachel
__________________________________________________________________
Daryl S said:
Rachel -
Your design is good, but then why would you add the order details to the
item details? Wouldn't the item details already be summed to the order
details?
Did you get the subtotals to work in the header or footer section of the
subforms? That is the first step before getting them to show up on the main
form.
--
Daryl S
Rachel said:
Actually Daryl they are related to each other in that ItemDetailsSubform is a
subform of OrderDetailsSubform which is a subform of the AddAnOrderandDetails
form.... There is the OrderID, OrderDetailsID and the ItemDetailsID.
Is this not a good design?
Rachel
Daryl S said:
Rachel -
Since the three are linked by a single field, I assume OrderDetailsSubform
and ItemDetailsSubform are not related to each other, and that they are both
on the AddOrdersForm. If this is not correct, then please clarify.
You should be able to get the total on the main form like this:
Me.txtTotalPrice = Me.[Order Details Subform].Form![txtOrderDetailsTotal] +
Me.[Item Details subform].Form![txtItemDetailsTotal]
Do your totals in the subforms work? I would think you want to switch the
Sum to the outside and Nz to the inside, like this:
Change =Nz(Sum([Extended Price]),0)
to =Sum(Nz([Extended Price],0)) 'You can use nz(Extended Price],0)
on the subform, too.
You should test the totals on both subforms before fixing the main form.
--
Daryl S
:
I have an AddOrdersForm, an OrderDetailsSubform and an ItemDetailsSubform.
The 3 are linked by the OrderID.
In the OrderDetailsSubform footer I have txtOrderDetailsTotal with
=Nz(Sum([Extended Price]),0) as the control source.
In the ItemDetailsSubform footer I have txtItemDetailsTotal with
=Nz(Sum([ExtendedToppingPrice]),0) as the control source.
On AddOrderForm I have txtTotalPrice.
I am trying to add txtOrderDetailsTotal and txtItemDetailsTotal in
txtTotalPrice but I keep getting ERROR#.
I have tried the following in the txtTotalPrice control source:
=[txtOrderDetailsTotal]+[txtItemDetailsTotal]
= [Order Details Subform].Form![txtOrderDetailsTotal] + [Order Details
Subform].Form![Item Details subform].Form![txtItemDetailsTotal]
I have tried:
Private Sub Order_Details_Subform_Exit(Cancel As Integer)
Me.txtTotalPrice = [Order Details Subform].Form![txtOrderDetailsTotal] +
[Order Details Subform].Form![Item Details subform].Form![txtItemDetailsTotal]
End Sub
I have also tried using a query but no luck.
Is it possible to calculate based on 2 different subforms?
Can someone please crack this one for me!
Thanks so much.
