Calculated fields for main form from subform 2

[VisaManiac]

I have a "Customer Orders" form that contains a "Order Lines" subform. Within the order lines subform it has the fields OrderNum (hidden field linked from the OrderNum in Customer Orders), Product Number, Product Price, Quantity and Line Total. Obviously Access knows to tie the OrderNum fields within both forms so that it only shows the associated orders with the same OrderNum for that particular customer. Here's the problem...on my Customer Orders form, I'd like to have a Order Subtotal field that sums all the associated Line Total together, but it won't work for me. How can I setup the calculation to only pull the associated ORderNum for that customer into the Order Subtotal field? If not, what's my workaround? Thanks a ton for any help on input on this!

 

[TheAceMan1]

How are ya VisaManiac . . .

Perhaps the following:
[ol][li]Sum the subform field by using a calculated control in the subform's form footer.[/li]
[li]Reference the calculated control from the main form to display the summed value:
ControlSource: =[purple]subFormName[/purple].Form![purple]SumControlName[/purple][/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[VisaManiac]

Thanks TheAceMan1, but I'm not very familiar with Access to know exactly what you're referring to test it out. Reading back on it I don't think I was very specific. Here's what I've dabbled with since posting the above...

I think I need to put together a query first, then reference the main form field to that query...here's what I've written but it still won't give me the results I want.

SELECT [ProductPrice]*[Quantity] AS LineTotal
FROM OrderLines
WHERE OrderLines.OrderNum IN
(SELECT DISTINCT(OrderLines.OrderNum)
FROM OrderLines);

The results give me the total for each line item, but I was hoping with the DISTINCT function it'll just give me the total of each OrderNum, not each LineTotal. I hope this gives a better illustration of what I'm looking for...HELP! Thanks a ton.

 

[PHV]

Perhaps this ?
SELECT OrderNum, Sum([ProductPrice]*[Quantity]) AS OrderTotal
FROM OrderLines
GROUP BY OrderNum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VisaManiac]

Oh MY!!!! SO SIMPLE!!! I was over-thinking it!!
THANK YOU VERY MUCH!!

 

[VisaManiac]

OK, now another problem. I wanted this OrderSubtotal field to calculate and show up on my main Customer Orders form. I have the relationships setup, I have the above statement setup as a Query, and inside my form field that I want it to show the OrderTotal, the Control Source properties have the expression setup as

=[OrderLines_Calculation Query]!qryOrderSubTotal

However, when I go to form view, it shows "#Name?" instead of the order total. Now what I doing wrong? Thanks again for any input on this!