Auto Sum

What is autosum?

Autosum is sample utility developed to emulate excel autosum - when you select range of cells their sum, average, minimum, maximum or count is displayed in right bottom corner.
When working with MS Access tables, querys and forms in datasheet view it is sometimes necessary to know the sum of certain cells. Common solution is to copy required cells, open MS Excel, paste and perform the calculations. Autosum project installs as MS Access Add-In and adds some functionality to datasheet context menus. You select cells, right-click and choose function (sum, average e t.c) and operation result appears in office assistant baloon.

What is it for?

Autosum may be used as saving time utility for people working with access tables.
However most of use is illustrative. Examining code could help in:
a) Creating Access Add-ins
b) Creating custom menus (including context menus) programmatically
c) Working with Datasheet as with form
d) Working with recordsets
e) Working with office Assistant

Downloading

To download file press here. If you for some reasons can not download it, you can create it yourself as described below.
To create Autosum open MS Access, create new database named AutoSumEng.mda.
Create two modules within it named AS and IN.
Copy code from this page to respective module.
Create a table named USysRegInfo with fields:
SubKey - Text 255
Type - Long Integer
ValName - Text 255
Value - Text 255
and fill them with following values:

SubKey	Type	ValName	Value
HKEY_CURRENT_ACCESS_PROFILE\Menu Add-Ins\&AutoSum
HKEY_CURRENT_ACCESS_PROFILE\Menu Add-Ins\&AutoSum	1	Expression	=Install()
HKEY_CURRENT_ACCESS_PROFILE\Menu Add-Ins\&AutoSum	1	Library	|ACCDIR\AutosumEng.mda

Your autosum is ready to install.

Installation

Autosum installs as Add-In. To install Autosum, first download it and save to local disk. Then open any database (not Autosum) and choose Tools->Add-Ins->Add-In Manager ->> Add New and point to path, where you saved AutoSum to. After that, Add-In manager will copy Autosum to Access directory and you will not need file on the disk where you originaly saved it to. You need to perform installation only once. To uninstall Autosum use Uninstall option in Tools -> Add-Ins -> Add-In Manager.

Using

Once installed, you can use autosum with all Access databases on your machine. To start Autosum you need to press Tools -> Add-Ins -> AutoSum. AutoSum functions will be available in context menus until you close database.
To use Autosum you need to open table or query and select range of cells (you can select whole row or column or some rows or columns). Right-click and you will see additional menu items: sum, average, count, countnum, min, max and share. Click on function you want and you will get the result on the pop-up assistant baloon. Click anywhere on the screen and assistant will disappear. "Share" is function to calculate and display the share of each cell of sum of all cells selected.

Known Problems

Autosum is non-commercial project and distributed free. All responsibility for it's use rests on user. There are some known problems.
1) During installation Autosum sets the reference to it's functions library. Any database, within wich Autosum add-in started, has the references to Autosum library. If that database will be moved to another machine where there is no Autosum installed, error may occur, caused by missed library. Solution to this problem is to remove references to Autosum library before or after moving (Choosing Tools -> References --> uncheck Autosum). That error is harmless to database, but difficuilt to find - since error message will point to one of standard functions (Date, Format, e t.c...) saying that access can't find project or library.
2) Autosum tested on WinNT 4.0 machine with Access 97. Other combinations are not tested, however no problems expected.
3) Autosum may work incorrect with crosstab querys, since they have different structure.
4) On large tables (up to 30000 records) summing up will take time. This process can be optimized, but currently it may take some minutes.
5) Since office assistant used to display the result, if it is not installed, you'll have no result displayed.
6) CountNum and Average functions works slightly different from excel. If value of some cell can be interpreted as numeric it will be counted and ased in average, regardless to field format. For example if in text field stored "1" it will be counted.
Author will gladly accept any more bug reports and solutions for them.

Excel 2003 AutoSUM

The AutoSUM button, located on the Standard Toolbar in Excel 2003, is a shortcut provided for using the SUM function. When you click on the AutoSUM button, the SUM function is entered into the active cell.
To use the AutoSUM button

1. Click on the cell where you want the answer to appear.
   
2. Click on the AutoSUM button.
   
3. The function will auto select the closest range of data cells. The selected cells are surrounded by the marching ants.
   
4. Check to make sure that the selected range for the function is correct.
   
5. If it is correct, press Enter on the keyboard.
   
6. If it is incorrect, drag select with the mouse the correct range and then press Enter on the keyboard.

AutoSUM's rules for selecting a range
The order that AutoSUM follows in suggesting a range of data for the function is:

1. The uninterrupted group of cells containing data above the active cell.
   
2. The uninterrupted group of cells containing data to the left of the active cell.
   
3. If there is no data in the cells above or to the left, AutoSUM doesn't select a range, but waits for you to do so.