Subforms in Microsoft Access are used in many ways, in some situations, they are used as lines details to support the parent forms. For example, a sales invoice will have line details and cost of sales subforms, these subforms may not be used to just getting data from the data input entry clerk alone, they may also get data via formulas embedded in them from other tables. This is where calculated controls come into play.
It is important to ensure that our subforms capture relevant data to support management decision making, for example, a sales line detail subform may also calculate the cost of sales per each product line. That is okay and simple to achieve by simply using DSum to get the total costs from the purchases table or wherever the costs are stored divided by the sum of the total quantities purchased.
The challenge here is how to sum the calculate product cost of sales as mentioned above. If we were using Excel that was not going to be an issue, but in MS Access it is a thorn to sort out. Now let us assume that we have a control called txtSTDCost with raw source data coming through by using Dsum to sum (From purchases table) all costs related to the products divided by the sum of all products quantities to give us average product cost per line, just doing that we would have created a calculated control on txtSTDCost.
Suppose we move further to multiply the quantities sold by the average cost, it means that we would have created another calculated control called cost of sales (txtcostsales). The challenge now is how to sum this control called txtcostsales) on the parent form so that users are able to see the cost of sales per generated invoice.
HOW TO SORT OUT THE CHALLENGE ABOVE
First of all, we need to make sure that the calculated control content is copied to a new control as an absolute value since we also have an absolute quantities sold value(QtySold), once the aforementioned is achieved, we can now go to the subform footer and place a formula like Sum(QtySold * txtAverageCost) assuming our new absolute average cost control is called txtAverageCost , and simply reference it on the parent form control.
How to copy the content of a calculated control called txtSTDCost? Here we will use the VBA like below:
Private Sub txtAverageCost _Exit(Cancel As Integer) Me. txtAverageCost = Forms![frmCustomerInvoice].Form![sfrmLineDetails Subform]![ txtSTDCost] End Sub
The above code will fire once we leave the control called txtAverageCost. And the result will be the much needed absolute value called average cost. To avoid deleting our new figure pasted, just simply lock the control containing the value.