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 resetting 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
	...
	total = total + rcd("Quantity") * rcd("Price")
	...
	rcd.MoveNext
    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.

However, this fails... see below!

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 + rcd("Quantity") * (rcd("Price") - rcd("Rebate")) + rcd("ShippingCosts")

We will assume that there is a hidden ShippingCosts box.

This fails.

Why it fails? I'm not 100% sure, but it looks like the current data is only available in the current line as defined in your form (i.e. say you have a field named COLUMN_PRICE, then rcd("Price") of the price you are currently editing is defined in COLUMN_PRICE, not in rcd("Price"), it will be only once you get out of that row.)

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 rcd("ShippingCosts") will use the OLD value, not the new one.

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.CurrentRecord = 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 + rcd("Quantity") * (rcd("Price") - rcd("Rebate")) + rcd("ShippingCosts")
    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)

WARNING: Before I was saying you could use the Form.Recordset().AbsolutePosition number. This number is "random" (often not set to the correct value.) Instead, you want to use the Form.CurrentRecord. One important difference, the CurrentRecord starts counting at 1, when the AbsolutePosition starts counting at 0.

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 functions work properly.

Okay... but... the Refresh messes up all the current focus settings. So that's simply not good.

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 current record equals 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! )

Complete Sum 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! 
    Dim total As Double
    Dim pos As Long

    rcd = Form.RecordSet()
    If rcd Is Nothing Then 
        ' no recordset available yet   
        Exit Sub
    End If
    pos = 1
    rcd = rcd.Clone
    rcd.MoveFirst
    Do While Not rcd.EOF
        ...
        If Form.CurrentRecord = 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 + rcd("Quantity") * (rcd("Price") - rcd("Rebate")) + rcd("ShippingCosts")
        End If
        ...
        pos = pos + 1
        rcd.MoveNext
    Loop
    If Form.NewRecord Then
       ' There is a new record not yet in the Recordset
       total = total + Quantity * (Price - Rebate) + ShippingCosts
    End If
    FormTotal = total  ' Save the result in the form total box

Note: the "..." represent other code that you may want to have in your loop. It must be replaced or removed before you use this loop.