Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Diminishing Balance Calc in Query


This is an offshoot of the earlier Function Running-Sum in Query Column.  With few changes in the earlier Function RunningSum() we can easily calculate and find the loan balance-to-pay amount, after deducting the monthly paid amount, at each record level.

The loan amount is payable in monthly installments.   Our simple task is to show the diminishing balance of loan amount against each record-level installment amount in a separate Column of the Query.  The last record will have the remaining balance amount pending to pay.

Let us pretend that the Loan Repayable Total Amount is 1000.

Sample Query Recordset.

The sample installment payment detail records are taken from the earlier Post: Running-Sum in Query Column as given below.

The Query SQL that calls the DiminishingBal() Function.

The SELECT Query SQL that calls the DiminishingBal() Function, in a separate Column of the Query.

SELECT Table_Units.ID, Table_Units.Units, DiminishingBal([ID],"ID","Units","DiminishingQ1") AS DiminishingBalance
FROM Table_Units;

The Query Recordset Image, with the result in the last column,  is given below:

We are using the same Query record set used as a source for the earlier RunningSum() Function and used here also for demonstration purposes.  The Recordset should have a Unique value (Numeric or String) field and used as the first parameter to the Function.

Total Repayable Loan Amount kept in a separate Table.

The Total Amount to be repaid to the Bank (1000) is kept in a separate Table with the following structure:

The DiminishingBal() Function VBA Code.

The VBA Code of DiminishingBal() Function is given below:

Option Compare Database
Option Explicit

'Declare a Generic Object
Dim D As Object

Public Function DiminishingBal(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'Function: DiminishingBal()
'Purpose : Calculate Diminishing Balance in a separate Column
'The Query can be used as source for other Processing needs,
'for Form View or Report
'Author  : a.p.r. pillai
'Date    : December 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 Calcuating Running Sum in String Format
'4  Query-Name in String Format
'Remarks: The Key-Value Field should have Unique Numeric or
'String Values.
Static K As Long, X As Double, fld As String, y As Long
Dim p As Variant

On Error GoTo DiminishingBal_Err

y = DCount("*", QryName)
'If the Function is not called by the same Query
'then initialize Dictionary Object and Variables
If SumFldName <> fld Or K > y Then
   fld = SumFldName
   Set D = Nothing
   K = 0
   X = 0
End If

K = K + 1
If K = 1 Then 'The major process of the function starts here
    Dim DB As Database, rst As Recordset
    'Create and instantiate the Dictionary Object
    Set D = CreateObject("Scripting.Dictionary")
    'Get Loan Repayable Amount
    X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1")
    'Open the EMI Recordset
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
    'Calculate cumulative record-level summary and
    'add the value into Dictionary Object as it's Item
    While Not rst.EOF And Not rst.BOF
    'read the record summary field value and add it to total
         X = X - rst.Fields(SumFldName).Value
    'read current record key field value
         p = rst.Fields(KeyFldName).Value
    'add the total value to dictionay object
    'as Key, Item pair
         D.Add p, X
    ' repeat this process for all records
    'close recordset and remove the database objects
    Set rst = Nothing
    Set DB = Nothing
    'Retrieve the first item from Dictionary,
    'using the first Key passed as parameter,
    'and return to the function calling record in the Query
   DiminishingBal = D(IKey)
   'Subsequent calls with the record Key passed as parameter
   'will retrieve other record values from Dictionary and
   'returns to their corresponding records in the Query.
   DiminishingBal = D(IKey)
End If

'A control forcing to initialize the static variables
'when the program is rerun for the same query.
   If K = y Then
      K = K + 1
   End If

Exit Function

MsgBox Err & ":" & Err.Description, vbOKOnly, "DiminishingBal()"
Resume DiminishingBal_Exit
End Function

How the Function Works.

In the Global declaration area of the VBA Module, Variable D is declared as an Object.

The DiminishingBal() Function needs four parameters:

  1. A Unique Value Field (Numeric or String Values) is the first parameter.  The parameter dataType is declared as Variant
  • The Unique Value’s Field Name is the second parameter in String format.
  • The Loan Installment Value Field Name.
  • The Query Name is the fourth Parameter.
  • Four Static Variables K, X, fld, and y are declared.  They must be Static Variables to retain their values between repeated calls of the Function, from each record of the Query.  The Variable p is declared as a Variant Type normal variable, to hold the Key-Value (either Numeric or String) of each record.

    The DCount() Function takes the record count of the Query in Variable y. The Value in this Variable is used as a control to check when to Reset the Static Variable Values to their initial Values and to remove the Dictionary Object from memory.  This control is necessary if the same Query is run more than once, consecutively.

    1. If the value in control Variable K is more than the Query record count in variable y then resets the Static variables to their initial values and the Dictionary Object is deleted from memory.
    2. Or, If the installment value Field Name is different, from the field name saved in Variable fld during the last call of the function,  then it assumes that the Function is called from a different Query Column and resets the Static Variable Values. The Dictionary object is deleted from memory.

    Next, Variable K is incremented by 1.  When K=1 the main action of the Function starts.  The Database and Recordset Objects are declared in DB and rst Variables respectively.

    In the next executable statement Set D = CreateObject("Scripting.Dictionary") creates the Dictionary Object with the CreateObject() method and assigns it to the Object variable D, which was declared in the Global Area of the Module.

    There are other ways to declare and use this Object by adding the Microsoft Scripting Runtime File to the Microsoft Access Reference Library List.  After that you can create an instance of the Dictionary Object in the following way:

    Dim D As Dictionary
    Set D = New Dictionary

    If you are new to the Dictionary Object and its usage visit the Post: Dictionary Object Basics.  There are five Posts on this Topic and you can find the links at the end of this Page.

    Next we need the value of repayable total Loan Amount and retrieves it from it’s Table tblRepay with the Dlookup() Function in the statement: X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1").  There is only one record in the Table with ID number 1 and you may omit the criteria part.

    The Query Recordset is open to read records one by one. The first record’s amount, paid to the bank, is deducted from the Loan Amount (1000) in Variable X.  The Unique Key value of the record is retrieved from the record and saves in variable p, in the next statement.

    The balance loan amount, calculated after deducting the repaid Amount, is added to the Dictionary Object, with Dictionary Object’s  Add Method, as its Item Value, with the Unique Key field value in variable p as Dictionary-Key in the statement: D.Add p, X. The Dictionary’s Add method always adds its Item value in Key, Item pairs.

    Note: If the Key-Value is not Unique then the Add method fails and will end up with Errors.

    With the rst.MoveNext statement takes the next record for the same sequence of processing and adds the result value to the Dictionary Object.

    This way individual record value is deducted from the remaining balance loan amount at that level and added to the Dictionary Object as its Item.

    Note: Here, you may ask why the Dictionary Object is chosen to hold all the calculated values rather than in an Array.  Yes, It can be done but that method needs more statements to store and retrieve the values in a Two Dimensional Array. It will become more complicated when the Query Record’s Unique Key Value is in String form.  The Dictionary Object allows the value retrieved in either sequential or random order based on its Key.  Here, the Random method works fine with the Key-Value Type in the Numeric or String form.

    When all the record processing is complete the record set and Database Objects are closed.

    What you have to keep in mind at this point is that still the value in variable K=1 and the first Parameter IKey retains the first records Unique Id Value.  At the first record level call of the function DiminishingBal() itself, we have calculated all the record level balance loan amount values, one by one, and added the result to the Dictionary Object as its Items. The function parameter IKey still holds the first record’s Unique ID value. That is the reason why we have used a separate variable p for individual record key values while processing all the records.

    So, the entire record level processing is done during the first call of the function, initiated from the first record of the Query, and all the record level result values are held in temporary storage in the Dictionary Object.

    The next statement DiminishingBal = D(IKey) retrieves the first value and added to the Dictionary using the unique parameter value IKey  and returns the value to the calling first record of the Query.

    The next call from the second record of the Query increments the variable K by 1 (now K=2) and the program takes the ELSE path of the IF. . .Then statement and retrieves the second Item value from the Dictionary Object, using the IKey parameter and returns it to the respective record of the Query.

    The rest of the DiminishingBal() function call from the remaining records will route the program control through only the ELSE  path, because the value in Variable K is greater than one, retrieves the values from Dictionary Item, and returns it to the function calling record.

    The Next If . . . Then statement checks whether the value in variable K = y or notVariable y holds the total record count of the Query.  If it is found True then it assumes that the last call of the DiminishingBal() function has arrived.  At this point, the K Variable is made greater than the value in variable y.

    This is necessary to initialize the Static Variables during the rerun of the same Query. In case of any change made on the Source Data before rerun, it will not reflect on the balance amount calculated earlier because it will keep taking the ELSE path of the If . . . Then statement and retrieve the old value from Dictionary Object.

    The Demo Database, with all the necessary Objects and the Code Module, is attached for your convenience to download and try it out straight away.

    Dictionary Object Links.

    1. Dictionary Objects Basics
    2. Dictionary Object Basics- 2
    3. Sorting Dictionary Object Keys and Items
    4. Display Records from Dictionary
    5. Add Class Objects as Dictionary Items
    6. Update Class Object Dictionary Item

    Opening Access Objects from Desktop

    Frequently Used Methods.

    1. Set the Form Name in Display Form Option of Current Database in Access Options.

      BIG DEAL!, this is the first trick any novice learns when he/she start learning Microsoft Access.

    2. Create an Autoexec Macro with FormOpen Action with the required Form Name in the Property.
    3. Both the above options open a Form, always we wanted to open it first when the database is launched.

    Opening Form Directly, without any Changes in Database.

    We would like to launch a particular Form automatically tomorrow, to continue updating data on that form, without making any changes in the database for that.

    Or, You would like to print a particular Report, first thing in the morning without fail, then here is a simple trick.

    Note: Your Database's Navigation Pane must be accessible.

    1. Open the Database.

    2. Click the Restore Window Control-Button to reduce the Application Window size, so that the empty area of the Desktop is visible.

    3. Click and hold the Mouse Button on the required Form Name, in the Navigation Pane, drag, and drop it on the Desktop.

    4. Close the Database.

    5. Double-Click on the Desktop-Shortcut. The Form will be in the open state when the Database is open.

      You can open the following Objects directly, with Desktop-Shortcuts:

    Try it out yourself.

    1. MS-Access Class Module and VBA
    2. MS-Access and Collection Object Basics
    3. Dictionary Objects Basics
    4. Withevents and All Form Control Types



    MSA GURU : Access Tips & Tricks App

    • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

    Want to Post Free Ads on the Web



    Subscribe in a reader
    Your email address:

    Delivered by FeedBurner


    Popular Posts

    Blog Archive

    Powered by Blogger.


    Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form ImageList Control Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ListView Control Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code