We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), published on this Website with the Post Title: Auto-Numbering in Query Column, during January 2010. Hope you have come across that Post, if not you may visit the Page by following the above link.
The RunningSum() Function is written somewhat on similar logic of QrySeq() Function.
Before going into details let us take a look at some sample Images, before and after run of the new Function in a test Query Column.
A small Table with two Fields: Table_Units and with few records.
The SELECT Query: RunningSumQ1 recordset in datasheet view, with summary values in a separate Column, with the column name RunningSum, from where the RunningSum() Function is called from.
The SQL Code of RunningSumQ1 Query.
SELECT Table_Units.ID, Table_Units.Units, RunningSum([ID],"ID","Units","RunningSumQ1") AS RunningSum
A Report Designed using RunningSumQ1:
The Query Preparation Note.
Before diving deep into the VBA Code I want you to check the above sample data, to draw your attention to an important point while preparing the data for the RunningSum() Function.
- A unique ID Field, like PrimaryKey required in the Query, with either Numeric or String Data and strictly no duplicates in them.
- If this is not readily available in the Source Data, you may join (concatenate) two or more field values together, to create unique values in a separate Column, as a Key Field in the Query.
- If this method is followed then create a Test Query similar to the sample one given below, using the first Query as source, to find out whether any duplicates still exists in the Source Query or not.
Sample ID Field Record-Count Test Query:
SELECT RunningSumQ2.ID2, Count(RunningSumQ2.ID2) AS CountOfID2
GROUP BY RunningSumQ2.ID2;
The CountOfID2 Column result should be like the sample Image give below, with all Count values are showing as one.
- When all the ID Field values are unique then the CountOfID2 Column will have the value 1 in all records. Greater than one in any record means that those records have duplicate key values and needs to join some other field to eliminate duplicates.
- Once you are sure that all records have unique ID values then you may add other required fields in the first Query for the purpose you plan to use it, like Form or Report Source Query.
- Once you are ready with the Query data then it is time to add the function in a new Column in the Query, like: Summary:RunningSum([ID],”ID”,”Units”,”MyQuery”).
The RunningSum() Function VBA Code.
Option Compare Database
Dim D As Object
Public Function RunningSum(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'Purpose : Creates Running-Sum Value of a Field.
'The Query can be used as Source for other Processing needs.
'Author : a.p.r. pillai
'Date : November 2019
'Rights : All Rights Reserved by www.msaccesstips.com
'Parameter List, in the Order of it's placement
'1. Key Value Data Field
'2. Key-Field Name in String Format
'3. Field-Name for Calculating Running Sum in String Format
'4 Query-Name in String Format
'Remarks: The Key-Value Field should have Unique Numeric or
Static K As Long, X As Double, fld As String, J as Long
Dim p As Variant
On Error GoTo RunningSum_Err
If SumFldName <> fld Then
fld = SumFldName
Set D = Nothing
K = 0
K = K + 1
If K = 1 Then
Dim DB As Database, rst As Recordset
Set D = CreateObject("Scripting.Dictionary")
Set DB = CurrentDb
Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
While Not rst.EOF And Not rst.BOF
X = X + rst.Fields(SumFldName).Value
p = rst.Fields(KeyFldName).Value
D.Add p, X
Set rst = Nothing
Set DB = Nothing
RunningSum = D(IKey)
RunningSum = D(IKey)
If K > J then
Set D = Nothing
MsgBox Err & ":" & Err.Description, vbOKOnly, "RunningSum()"
Familiarising the VBA Code.
On the Global area of the Standard Module an Object Variable is declared with the name D.
The function RunningSum() is declared with four parameters.
- The Unique Key Field Value.
- The Key-Field Name in String format.
- The Summary Field Name in String format.
- The Query-Name in String format.
The returned value from function is Double precision number.
Four Static Variables are declared in the Function:
K – is a control variable.
X – to hold the Summary Values, added to it at record level.
fld – A control Variable to keep the Summary Field Name as a flag to ensure that the function runs for the same Query.
The Static Variables retain their old values during repeated calls of the Function.
Variable p is to hold the IDKey-value retrieved from the record. It is declared as Variant Type to accept either Numeric or String Key Value.
The Working Logic of the Function.
The statement If SumFldName <> fld Then checks whether the Key-Field Name passed to the function is different from last call of the Function. If it is different then it assumes that a different Query is passed to the function.
The Dictionary Object D is erased from memory and other variables are initialized.
In the next step the K Variable is incremented by one. When K=1 the function’s main task is initiated.
The Database and Recordset Objects are declared.
The D Object variable is instantiated as a new Dictionary Object, with the Object creation statement: Set D = CreateObject(“Scripting.Dictionary”).
By default, the Dictionary Object Reference is not added to the list of Microsoft Access Library Files. If you add it manually then you can declare and instantiate a Dictionary Object, like the Class Object of Access or Collection Object.
Note: If you are not familiar with Dictionary, Class Object or Collection Object, then we have all the information you need to learn the fundamentals about them, in this Website. The links are given at the end of this page. You may visit them to learn with sample code and Demo databases, available to download.
Adding Dictionary Object Reference File.
To add the Dictionary Object to your Database’s Library Files List do the following:
On the VBA Window, select Tools – – >References… and look for the file: Microsoft Scripting Runtime in the displayed list and put checkmark to select it.
Once you do this you can declare and instantiate a Dictionary Object as given below.
Dim D As Dictionary
Set D = New Dictionary
If you do this you have an added advantage of displaying it’s Properties and Methods, when you type a dot (D.) after it’s Object name, by intellisense automatically.
Next, the database object DB is set with the active database and the Query is opened as recordset in rst.
Within the While. . .Wend Loop the summary field and the unique key Field values are read from each record. The Summary field value is added to the Variable X. The Key value of record is written as Key Value of Dictionary Object and the current Value in X is written as Dictionary Object Item, in Key, Item pair.
The Dictionary Object Items are always written in this way. The Item can be a single value, an Array, Objects or Collection of Objects. All of them should have a Unique Key Value to retrieve the Item later.
The purpose of Key in Dictionary Object is similar to the function of Primary Key in a Table. We can retrieve any value Randomly or Sequentially from the Dictionary Object using the Key, like A = D(Key) or A = D.Item(Key).
In this way the cumulative summary value, at each record level, is added to the Dictionary Object as it’s Item, with unique Key. When all the record level processing is complete, the first record summary field value is returned to the function calling record by execuring the RunningSum = D(IKey) statement, from the first Dictionary Item. All the above actions are taking place when the control Variable K is equal to 1.
Subsequent Calls of the function with the Key Value parameter of each record retrieves the corresponding summary value of that record from Dictionary Item and returns it to the Query Column, that’s how it works.
Some Images of a sample Run done on the Products Table of NorthWind.accdb are given below.
Sample Query Run (Key Values are String Type) Data on Form.
SELECT Trim(Str([ID])) & [Product Code] AS ID2, Products.[Product Code], Products.[Product Name], Products.[List Price], RunningSum([ID2],"ID2","[List Price]","RunningSumQ2") AS RunningSum
The RunningSumQ2 Query is the Record Source of the Form.
Sample Run Data on Report.
The RunningSumQ2 Query is the Record Source of the Report.
Download Demo Database.
MS-Access Class Module and VBA
MS-Access VBA Class Object and Arrays
MS-Access Base Class and Derived Objects
VBA-Base Class and Derived Object-2
Base Class and Derived Object Variants
MS-Access Recordset and Class Module
Access Class Module and Wrapper Classes
Wrapper Class Functionality
MS-Access and Collection Object Basics
MS-Access Class Module and Collection Objects
Table Records in Collection Object
Dictionary Objects Basics
Dictionary Object Basics-2
Sorting Dictionary Object Keys and Items
Display Records from Dictionary
Add Class Objects as Dictionary Items
Update Class Object Dictionary Item
MS-ACCESS EVENT HANDLING
Withevents MS-Access Class Module
Withevents and Defining Your Own Events
Withevents Combo List Textbox Tab
Access Form Control Arrays And Event
Access Form Control Arrays And Event-2
Access Form Control Arrays And Event-3
Withevents in Class Module for Sub-Form
Withevents in Class Module and Data
Withevents and Access Report Event Sink
Withevents and Report Line Hiding
Withevents and Report-line Highlighting
Withevents Texbox and Command Button
Withevents Textbox Command Button
Withevents and All Form Control Types