Simple Sum

When you create a form in MS-Access and include a sub-form with a table in it, you may want to compute the sum of a field. This is done simply by creating a TextBox and writing the following in that box content:

=Sum([Column-name])

Where "Column-name" is (obviously) the name of the column you want to sum up.

This is neat, but it breaks if the specified column has an unsupported type. Very strange, but for me it breaks with a currency column! That feels a bit weak to say the least. According to my testing and what I have found on the net, it should work with any numbers.

Note that the possibilities are actually endless. So if you have a set of columns called Quantity and a Price, you could write this:

=Sum([Quantity]*[Price])

and that gives you the total costs in that table (before tax, if you have to handle tax on a per row basis, good luck with that! Well... see the other solutions below! )

Note that I tried on my system and my Price is marked as Currency and thus the computation fails...

Simple Sum of a Product whatever the type

Forms in a MS-Access system are linked to a table via a Recordset.

If you know about SQL databases, a Recordset is an equivalent to a Read/Write CURSOR.

The idea is pretty simple: You have fields and each one of them is assigned a name that happens to be the name of a column in a table. That matches perfectly and allows you to display the content of records painlessly (no! really!).

This works with all sorts of tables including those with multiple records showing (like a spreadsheet).

Today I had to compute the total amount of an invoice where one form is a table of items. I have a simple loop going through the record and multiply the data in the Quantity and Price columns and generate a total of all the rows. This gives me my current total amount.

My first version would not work properly and my cursor would get stuck at the bottom of the form (last valid cell.) I tried all sorts of things and determine that the problem was with reseting and then reading the content of the recordset. But I definitively need to do that if I want to be able to compute my grand total!

The solution is actually very simple: You need to clone the form record set and use the clone for the computation.

    Dim rcd ' As RecordSet2 -- somehow, that does not work for me...
            ' MS-Access says that Recordset2 does not exist.
            ' See Can't find msaccess_recordset to include! 

    rcd = Form.RecordSet()
    If rcd Is Nothing Then 
	' no recordset available yet   
	Exit Sub
    End If 
    rcd = rcd.Clone
    rcd.MoveFirst
    Do While Not rcd.EOF
	row = rcd.GetRows(1)
	...
	total = total + row(1, 0) * row(2, 0)
	...
    Loop

Here I assume that the quantity is in column 1 and the price in column 2. Column 3 is probably the total price for that row and column 0 the description of the item being purchased.

P.S. No! The total could not simply be attached to the table with an SQL statement, even a complicated one. It would have been easy to write SELECT SUM(Quantity * Price) FROM ... and simply use the one result. But since the cells change all the time, we need to recompute the results all the time. And that works only if you dynamically compute the result. (although you could certainly use another SQL statement... but that's a waste of memory, although it could be faster for large tables!)

Complex Sum of a Product

Now let's assume for a moment that you have a complicated table. That never happens anyway, but well...

My table is something like this:

As you can see, fairly standard table. It has an item with a quantity and a price. Also, there is a possible rebate and a button to define shipping information.

The computation goes like this:

=Sum([Quantity] * ([Price] - [Rebate]) + [ShippingCosts])

The Sum() function won't work because I have Currency columns. But that's how the computation would work otherwise.

Now, to make my table function, I have that button for the Shipping. This is because each item can be sent in a separate box with a separate cost and a tracking number associated to it. Thus, I open a sub-form, ask for the info to the user, the user hits Save and you're right back in the table. At that point, I call my function to recompute the total costs of the entire shipment.

In order to keep the info about the shipping, I have hidden TextBoxes (one for each field defined in the sub-form, for the sake of my example, I'll talk about one: ShippingCosts.

In order for the sum to work, I have a loop as defined before and I have my computation like this:

total = total + row(1, 0) * (row(2, 0) - row(3, 0)) + row(4, 0)

We will assume that the hidden ShippingCosts box is #4 and that the others are in order.

This fails.

Why it fails, I'm not too sure, but it does. The fact is that the sub-form saves the data in the current row using the row field names. So assuming the form is called Invoice and its table sub-form is called InvoiceSubform, the Save function of the Shipping sub-form would do something like this to save the data in the sub-table:

    p = Forms("InvoiceSubform")
    p("ShippingCosts").value = SHIPPING_COSTS

I assume that the Shipping sub-form has a field named SHIPPING_COSTS.

When back into your InvoiceSubform, you will see that the column named ShippingCosts is set to the new value. Everything worked according to plan! What is still wrong, however, is the value in the Recordset. Thus, the loop using the row(4, 0) will use the OLD value, not the new one.

I'm not too sure how you can force the row current data to be saved in the Recordset. I noticed that if I were to click on another row, then the Recordset is good. In the meantime, it is wrong. The only solution I have at this time is this:

    If Form.Recordset().AbsolutePosition = pos Then
       ' Here use the current row (i.e. field names)
       total = total + Quantity * (Price - Rebate) + ShippingCosts
    Else
       ' Here use the row() array
       total = total + row(1, 0) * (row(2, 0) - row(3, 0)) + row(4, 0)
    End If

As you may notice, I simply check: Is this the current row? by comparing the absolute position of the recordset and the pos variable that I set to 0 at the start and increment by one at the end of each loop. (Hint: Optimize by copying the absolute position in a variable)

This fails too.

Although this second solution works with a click on a button and closing of the sub-form, when clicking between rows, it fails. There is actually a way to make the first solution work. You need to call one function before reading the form Recordset() rows. This is the Refresh function.

    Dim rcd ' As RecordSet2 -- somehow, that does not work for me...
         ' MS-Access says that Recordset2 does not exist.
         ' See Can't find msaccess_recordset to include! 
    Refresh
    rcd = Form.RecordSet()
    While ... 

As you can see, by adding a Refresh call just before the loop, the data will be saved from whatever temporary variable they are defined in to the RecordSet. And this makes all the row function properly.

Complex Sum including the new record, maybe?

Being a perfectionist, I skip things that would take ages to write and make functional, but adding the current row when it is not even defined in the Recordset at all is certainly a good idea and it is very simple.

The fact is the form has a flag to tell you whether the last row with data is new or not. This seems very complex to me too... Not my choice! To know whether that row exists, use the following test and add the total like we've done in the previous example:

    If Form.NewRecord Then
       ' There is a new record not yet in the Recordset
       total = total + Quantity * (Price - Rebate) + ShippingCosts
    End If

As you can see it is a repeat of the computation when the absolute position equal your position.

Now you have a correct total (hmmm... assuming you did not need to compute taxes. In my real scenario, each line may or may not have a sales tax added! )