I don't think what you are trying will work. I haven't tried using a SUM() in a TextBox on a Form, just a Report, so I'm not 100% sure. But I wouldn't recommend that approach anyway. I've found it's easier to build either a Function or Query to return Totals and other calculations that will end up being in multiple places in your Application. This way if the formula changes there is only one place to make the change. It doesn't sound like much, but maybe your Total takes into account a base margin of 20%, then the owner decides to bump it up to 25%, or to make it change based on product line. Having only one place to make this change would make this modification much easier and less error prone.
Usually, for Totals I make one Query that performs all the LineItem calculations. Then I make a second Query, based on the previous Query that performs the Totals on Groups of LineItems, where the Group is determined by the PrimaryKey, like OrderNumber, InvoiceNumber, or QuoteNumber.
Once the Queries are made, they can be included as a link in a RecordSource or sometimes you can change the RecordSource over to the Totals Query and use the total values as Bound Fields. The caveat is that sometimes doing so will make the RecordSource ReadOnly.
Another option is to use Functions to retrieve values from the Query as needed. In your case, you could create a Function in which you pass the PrimaryKey of the MainForm's record and it would return the TotalValue from the Query that has all the Totals in it. Here is a basic example:
- Public Function getOrderTotals(Byref lPrimaryKey As Long) As Currency
-
getOrderTotals= Dlookup("TotalValue", "TotalsQuery", "PrimaryKey=" & lPrimaryKey )
-
End Function
There are a lot of other options and ways of doing this sort of thing, but this is the model that I try to use because you can then turn around and make as many Reports and Forms as people want, and the Totals always work the same.
1
