HOW TO SUM A CACULATED CONTROL FROM THE SUB FORM IN MS ACCESS?

Published on
3,134 Points
134 Views
Subforms can still help us achieve what we want to achieve as long as we understand how to use the MS Access tools.

INTRODUCTION

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.

DETAILS

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.

1. If we simply sum the control called txtcostsales), example Sum(txtcostsales) we will get an error because the cost of sales is derived from another calculated control called txtSTDCost which is multiplied by an absolute value called quantities (QTY Sold). For sure the sum must be done within the subform footer and then referenced to the parent form.

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.

0