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 instalments.   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 recordset used as 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 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 the Variable D is declared as an Object.

The DiminishingBal() Function needs four parameters:

  1. A Unique Value Field (Numeric or String Values) as first parameter.  The parameter data Type is declared as Variant.
  2. The Unique Value’s Field Name as second parameter in String format.
  3. The Loan Installment Value Field Name.
  4. The Query Name as 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, that 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 it’s 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 it 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 it’s 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 it’s 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 it’s 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 retrievel in either sequential or random order based on it’s Key.  Here, the Random method works fine with the Key Value Type in Numeric or String form.

When all the record processing is complete the recordset 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 it’s 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 a temporary storage in the Dictionary Object.

The next statement DiminishingBal = D(IKey) retrives the first value 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 required Form Name in the Property.
  3. Both the above options opens a Form, always we wanted to open 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 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

Running Sum in MS-Access Query


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, with few parameters matching the 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
FROM Table_Units;

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.

  1. A unique ID Field, like PrimaryKey required in the Query, with either Numeric or String Data and strictly no duplicates in them.
  2. 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.
  3. 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.
  4. Sample ID Field Record-Count Test Query:

    SELECT RunningSumQ2.ID2, Count(RunningSumQ2.ID2) AS CountOfID2
    FROM RunningSumQ2
    GROUP BY RunningSumQ2.ID2;

    The CountOfID2 Column result should be like the sample Image give below, with all Count values are showing as one.

  5. 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.
  6. 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.
  7. 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
Option Explicit

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
 'Function: RunningSum()
 '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
 'String Values.
Static K As Long, X As Double, fld As String, y as Long
Dim p As Variant

On Error GoTo RunningSum_Err

y = DCount(“*”,QryName)

If SumFldName <> fld OR K > y Then
    fld = SumFldName
    Set D = Nothing
    K = 0
 End If

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)
 End If

If K = y then
   K = K + 1
End If

Exit Function

MsgBox Err & ":" & Err.Description, vbOKOnly, "RunningSum()"
Resume RunningSum_Exit
End Function

VBA Code Line by Line.

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.

  1. The Unique Key Field Value.
  2. The Key-Field Name in String format.
  3. The Summary Field Name in String format.
  4. The Query-Name in String format.

The returned value from function is Double precision number.

Four Static Variables are declared:

  1. K – is a control variable.
  2. X – to hold the Summary Values, added to it at record level.
  3. 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 will retain their 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, as  working models 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.

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, as 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 values 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=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
FROM Products;

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.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality


  1. MS-Access and Collection Object Basics
  2. MS-Access Class Module and Collection Objects
  3. Table Records in Collection Object


  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


  1. Withevents MS-Access Class Module
  2. Withevents and Defining Your Own Events
  3. Withevents Combo List Textbox Tab
  4. Access Form Control Arrays And Event
  5. Access Form Control Arrays And Event-2
  6. Access Form Control Arrays And Event-3
  7. Withevents in Class Module for Sub-Form
  8. Withevents in Class Module and Data
  9. Withevents and Access Report Event Sink
  10. Withevents and Report Line Hiding
  11. Withevents and Report-line Highlighting
  12. Withevents Texbox and Command Button
  13. Withevents Textbox Command Button
  14. Withevents and All Form Control Types


Date2Text and Text2Date Functions


We already have some frequently used Report Footer formatting simple functions, like Report Page Number formatting function =PageNo([page],[pages]),  output: Page: 1/20 –> Page: 20/20, Report Period Function =Period([StartDate], [EndDate]), output : Period: 15/09/2007 To 30/09/2007 and =Dated()  Function, output: Dated: 15/09/2007 on Report Footer.  Even though they are simple Report Header/Footer formatting functions it saves report design time.  Check the following Links, if you have not yet come across those Functions earlier:

Useful Report Functions.

Continued on Page 2/- an indicator Label on Report Page Footer, on multi-page reports.

Our new Function formats the Date Value into the following sample Text form:

  Sunday, 27th October 2019.

The Date2Text() Function Code.

Public Function Date2Text(ByVal dt As Date) As String
Dim txt As String, num As Integer

num = Day(dt)

   Select Case num
       Case 1, 21, 31
          txt = "st "
       Case 2, 22
          txt = "nd "
       Case 3, 23
          txt = "rd "
       Case 4 To 20, 24 To 30
          txt = "th "
   End Select
   Date2Text = WeekdayName(Weekday(dt)) & "," & Day(dt) & txt & MonthName(Month(dt)) & " " & Year(dt)
End Function

Copy and paste the above Code into the Standard VBA Module, save and compile the Code.

Let us try out the Code directly from Debug Window. Press Ctrl+G to display the Debug Window, if it is not already visible on the VBA editing Window.

Sample Test Runs.

D = #27-10-2019#

? Date2Text(D)
Result: Sunday, 27th October 2019


? Date2Text(D)
Result: Tuesday, 22nd October 2019


? Date2Text(D)
Result: Friday, 11th October 2019

? Date2Text(Date)
Result: Thursday, 31st October 2019

Weekday 1 to 7 is Sunday to Saturday. This depends on your Computer's Regional Settings. If not correct in your case then change it on the Regional settings on your Computer.

Scope of this Function.

The Date2Text() Function can be placed in Text-Box on Report Header, use it on Query’s Date-Field Column, or on the Main Form (Main Switchboard or Control Screen etc.) as general info.

The Text2Date() Function.

The Date2Text() Function’s complementary Function Text2Date() VBA Code is given below.

Public Function Text2Date(ByVal txtDate As String) As Date
Dim S, dt As String
    S = Split(txtDate, " ")
    dt = Str(Val(S(1))) & "-" & S(2) & "-" & S(3)
    Text2Date = DateValue(dt)

End Function

The date converted into Text form can be changed back into valid date format with the Text2Date() Function. There is no validation check performed on the input value for errors and expected to pass the parameter value in the correct input format, same as the Date2Text() Function output.


? Text2Date("Thursday, 31st October 2019")

Result: 31-10-2019

Caution: If the parameter value is entered manually then there should not be more than one space between each segment of the date text.

  1. Days in Month Function
  2. Custom Calculator and Eval Function
  3. Rounding Function MRound() of Excel
  4. Proper Function of Excel

Call Function From MouseMove Event Property


This is about running a User-Defined Function (say myFunction(Parameter)) on the Mouse Move Event Property of Text Boxes.  When the Mouse Move Event occurs the Text-Box name must be passed as Parameter to the function dynamically.

This question was asked in an Access User's Forum (www.accessforums.net), in the Forms Category of Posts, by a member, seeking suggestions for a solution.  A demo database was posted by me there, twice on page-5, but the last one is the final version. 

I thought it will be useful to my readers and presented here for you, with details of this difficult requirement and how this Object Oriented Programming approach solved the puzzle.

Manual Option.

This is easy to set up, if it is manually entered =myFunction("Text1")  on each Text Box's Mouse Move Event Property

But, the requirement is to pass the Text-Box Name as parameter dynamically to the Function.  It means that we should somehow get the Text Box Name from the Mouse Move Event and pass it as parameter to the function, placed on the same Mouse Move Event Property..

To get to know the real situation that demands this method, digest the following requirement of an Access Application:

Complexity of Requirements.

Assume that you are developing a database for a movie ticket booking Application and needs around 350 or more text boxes on the Form, for a graphical design of the seating arrangement.  Each Text-Box represent a single seat in the cinema hall, in an arrangement of several rows & Columns (i.e. each row have several seat positions) and each Seat is having a unique identity number (that is text box name), indicating it's position in the auditorium, like Row-A, Seat No.5 (A5) or B1 etc. The text-box text will show Booked or Vacant  depending on it's current status.

The idea is, when the mouse moves over the textbox (Seat) it should display the Seat Number (A5, or B1 etc.) on a dedicated Label on the Header or Footer Section of the Form, to help the customer to look for his choice of Seat Numbers and book the Seat(s).

A simple Form with several Text Boxes and a label on the top is given below to get an idea of sample arrangement of Text Boxes, to try out methods to solve this problem.

PS: The technical details presented above may have some lapses or may form suggestions in the minds of the reader.  That is not important, the core point is how do we manage to get the Text-Box Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed in the Mouse Move Event Property.

Why Manual Method not Acceptable.

So, writing =myFunction("A5") or =myFunction("B1") etc. in each one of  350 Text Box's Event Property is lots off work.  Besides that, if any change of arrangement of Seats or reworking of the Seat Numbering scheme become necessary then all the text box Properties have to undergo manual changes. 

Another option available is to set the Control Tip Text Property with the Text Box Name. When the mouse-pointer rests on the Text-Box, after a brief delay (the delay is not acceptable), the Seat Number is displayed from Control tip text property by the System.  Modifying the Control Tip Text Property is easy and can be done dynamically on the Form_Load() Event Procedure. 

But, the database designer insists on passing the Text Box Name as parameter to the Function.  Besides displaying the Text-Box Name on the designated Label Caption and the Function may have other issues in the program to take care off as well, on the Mouse Move Event. 

The Difficult Question.

Even though it sounds like a simple issue, the difficult question is how do we get the Text-Box name, say Text1, from the Name Property, when the mouse moves over that Text-Box and pass the name as parameter to the Calling Program?   Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinates on the text box (or on any other control it moves)  and this Event have some default parameters: Button, Shift, X and Y coordinates of the Mouse Pointer on the Control.  But not the Control Name among them.

The Programming Road-Blocks.

There are times that we face road-blocks on solving issues, when conventional programing approaches doesn't give the correct solutions.  But such issues can be easily handled by few lines of Code through Object Oriented Programming.  This is a classic example, easy to understand and does the job with few lines of code.

Access Class Module Objects.

We have already covered earlier the fundamentals of Access Class Modules and Objects based programming.  If you are not familiar with stand-alone Access Class Modules and Objects then  the links are given at the bottom of this page for you to start learning the basics.

The Easy Solution.

To solve the above narrated issue we have used few lines of Code in the Access Class Module Objects (both Form and stand alone Class Modules) and used Collection Object to organize several instances of the Class Module Objects, rather than using Arrays.

The General purpose Text-Box Object Class Module: ClsTxt Code:

Option Compare Database Option Explicit Private WithEvents txt As Access.TextBox Private frm As Access.Form Public Property Get pFrm() As Access.Form Set pFrm = frm End Property Public Property Set pFrm(ByRef vNewValue As Access.Form) Set frm = vNewValue End Property Public Property Get pTxt() As Access.TextBox Set pTxt = txt End Property Public Property Set pTxt(ByRef vNewValue As Access.TextBox) Set txt = vNewValue End Property Private Sub txt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) '------------------------------------------------ 'The first MouseMove Event, of each TextBox, 'comes into this sub-routine. 'The MouseMove Event Property is set with the Function: '"=RunMouseOver('Textbox_Name','Form_Name')" 'with the TextBox & Form Names as Parameters. 'Subsequent MouseMove Events Calls the Function 'directly from Standard VBA Module1, 'control will not come into this sub-routine, any more. '------------------------------------------------

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')" End Sub

Two Class-Module Properties, the Access.TextBox  and Access.Form Object are declared in txt and frm object Variables with Private scope, respectively.  The txt Property is declared with WithEvents key word to capture Events originating from Text Boxes on Form. The next twelve lines of Code are for assigning and retrieving objects in Text Box and Form Properties with Set/Get  Property Procedures.  This will prevent direct access to the Class Module Properties txt and frm from outside.  Up to this point it is the Text Box Object's common feature for assigning and retrieving values to and from the Object Variables.  The frm Property is not used here.

The sub-routine part is what we are interested in.  Any number of Text-Box based Event Procedure sub-routines can be written here rather than directly on the Form's Class Module.

The txt_MouseMove() Event.

The Text Box's first Mouse Move Event transfers control into the txt_MouseMove() Subroutine.  There is only one executable statement in the sub-routine, that over-writes the Text Box's Mouse Move Event Property Value. 

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')"

We can get the Text-Box name from the txt Property.  The Text-Box Mouse Move Event Property (initially set as "[Event Procedure]" in the Form_Load() Event Procedure) is replaced with the Function "=RunMouseOver('" & txt.name & "')" and passes the Text-Box name as string Parameter.  The subsequent Mouse Move Events will call the RunMouseOver() Function in Standard Module, from the Mouse Move Event Property and never comes back to the above sub-routine txt_MouseMove() any more. 

So, the first Mouse Move Event on any Text Box will do the trick and other Text Boxes will wait for their turn for a Mouse Move Event to take place.

The simple RunMouseOver() Function Code will be presented later in this page.

Form3 Class Module Code.

The Form's (Form3) Class Module VBA Code is given below:

Option Compare Database
Option Explicit

'Declare Class ClsTxt as Object F
Private F As ClsTxt
'Declare Collection Object as C
Private C As Collection

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection 'instantiate Collection Object

For Each ctl In Me.Controls 'scan through the controls
   If TypeName(ctl) = "TextBox" Then ' Take only Text Boxes
        Set F = New ClsTxt 'instantiate ClsTxt Class Object
            Set F.pFrm = Me 'Assign Form to pFrm Property
            Set F.pTxt = ctl 'Assign TextBox to pTxt property
            'enable mouse move event
            F.pTxt.OnMouseMove = "[Event Procedure]"
        C.Add F 'add ClsTxt Object to Collection
        Set F = Nothing 'remove the ClsTxt object instance from memory
    End If

End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Erase the Collection Object when Form3 is closed.
    Set C = Nothing
End Sub

In the global declaration area Class Module ClsTxt is declared as Object F and Collection Object as C.

In the Form_Load() Event Procedure we scan through Form3 controls and takes only Text Box controls.  The Form Object and Text-Box controls are assigned to the F.pFrm and F.pTxt Properties of ClsTxt Object.

The F.pTxt Object's OnMouseMove() Event Procedure is enabled, so that when it happens the control goes to the txt_MouseMove() sub-routine of Class Module instance of ClsTxt for the first time. In the next step ClsTxt Object instance F is added to the Collection Object, as it's Item.  In the next step the ClsTxt Object instance F is cleared from memory.  A new F object instance is created for the next Text Box.  This is necessary to identify each instance of Text Box Object, with a different internal reference, related to each Text Box added to the Collection Object as it's Item.

This process repeats for all the Text Boxes on Form3.

When the Form is closed the Form_Unload() Event executes and the Collection Object is cleared from Memory.

When Form3 is open these initializing steps are performed and all the Text Box Controls are enabled with the Mouse Move Event, added to the Collection Object and stays in memory till Form3 is closed.  Each Text Box's Mouse Move Event is handled by their respective ClsTxt Object instance added in the Collection Object.

The RunMouseOver() Function Call.

When the User moves the mouse over a Text Box (say Text-box name A1) for the first time the Mouse Move Event executes and calls the txt_MouseMove() Event Procedure in the ClsTxt Object instance, for that Text Box, in the Collection Object item.  In this procedure the TextBox's MouseMove Event Property is modified and inserted with the =RunMouseOver("A1")  Function with the Text Box name A1 as Parameter.

The second Mouse Move Event onwards it calls the RunMouseOver() Function from the Standard Module1.  The VBA Code of this Function is given below.

Option Explicit

Public Function RunMouseOver(strN As String)
    Screen.ActiveForm.Controls("Label0").Caption = strN

End Function

The RunMouseOver() Function receives the text-box name as parameter.  The statement addresses the Label0 control, directly through the Screen Object ActiveForm route  and changes the Label's Caption with the Mouse Moved Text-box Name.

The RunMouseOver() Function can be modified to pass the Form's Name as second parameter and can be used to address the Label0 control as Forms(strForm).Controls("Label0").Caption = strN.  This is avoided to keep the parameter expression simple.

When the Mouse is moved over other Text Boxes the same procedure is repeated for that Text Box Object instances in the Collection Object.

When Form3 is closed the Collection Object instance C, containing all Text Box's ClsTxt Class Object instances, is cleared from memory.

The Function RunMouseOver() assigned to Text Box's Mouse Move Event Properties are cleared (as they are assigned dynamically) and the Property will remain empty.

Next time when Form3 is open everything falls into place again and ready for action.  So everything controlled by the Object oriented Programming and happens dynamically.  This sample database is uploaded as solution to the Access User's Forum Page 5, where several alternative options are suggested by other members of the Forum.  You may visit this Group for suggestions to solve your issues and for help on matters related to Queries, Reports, Forms etc.

The Demo Database is attached and may Download and try it out yourself.

Class Module Tutorials.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Update Class Object Dictionary Item on Form

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is always rounded up.  When number of digits is greater than 0 (zero), then the number is rounded up to the specified number of decimal places. If number of digits specified is 0 (zero), then the number is rounded up to the nearest integer.

Visit the following link for more details on Roundup() Function:

The ROUNDUP() Function of Excel in Access.

The ROUNDDOWN() Function of Excel does the opposite of ROUNDUP() Function.  When the number of digits specified is greater than 0 (zero) then the number is rounded down to the specified number of decimal Places.  If the number of digits specified is 0 (zero) then the number is rounded down to the nearest integer.


ROUNDDOWN(Number, num_digits)

Number: RequiredAny Real Number.

num_digits: Required, Number of Digits the Number to Round Down to.

Public Function ROUNDDOWN(ByVal Num As Double, ByVal num_digits As Integer) As Double
'ROUNDDOWN() Function of Excel Redefined in MS-Access
'Author: apr pillai
'Date  : Sept 2019
'Rights: All Rights Reserved by www.msaccesstips.com

Dim S1 As Integer, S2 As Integer

S1 = Sgn(Num)
S2 = Sgn(num_digits)

Select Case S1
    Case 0
        ROUNDDOWN = 0
        Exit Function
    Case 1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Num) * S1
        Case 1
            ROUNDDOWN = (Int(Num * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = Int(Num / (10 ^ num_digits)) * 10 ^ (num_digits) * S1
    End Select
    Case -1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Abs(Num)) * S1
        Case 1
            ROUNDDOWN = (Int(Abs(Num) * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = (Int(Abs(Num) / (10 ^ num_digits)) * 10 ^ num_digits) * S2
    End Select
End Select

Exit Function

MsgBox Err & " : " & Err.Description, , "ROUNDDOWN()"
End Function

The ROUNDDOWN() Function is not field tested for accuracy, use it at your own risk.

The Function is developed based on the sample output given in the Microsoft Help Document. The Microsoft Excel Help Document extract is reproduced below for your information.

=ROUNDDOWN(3.2, 0)Rounds 3.2 down to zero decimal places.3
=ROUNDDOWN(76.9,0)Rounds 76.9 down to zero decimal places.76
=ROUNDDOWN(3.14159, 3)Rounds 3.14159 down to three decimal places.3.141
=ROUNDDOWN(-3.14159, 1)Rounds -3.14159 down to one decimal place.3.1
=ROUNDDOWN(31415.92654, -2)Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.31400
  1. Roundup Function
    of Excel in MS-Access
  2. Proper Function of
    Excel in Microsoft Access
  3. Appending Data from
    Excel to Access
  4. Writing Excel
    Data Directly into Access
  5. Printing MS-Access
    Report from Excel
  6. Copy Paste Data
    From Excel to Access2007
  7. Microsoft Excel Power
    in MS-Access
  8. Rounding Function
    MROUND of Excel
  9. MS-Access Live Data in
  10. Access Live Data in Excel-
  11. Opening Excel Database
  12. Create Excel Word
    File from Access

WithEvents and All Form Control Types


So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and capture in stand alone Class Module, in various ways.  We have done demo runs to capture Events Raised from Form, like: AfterUpdate and LostFocus, in Class Module from several Text Boxes. 

The Class Object instance, one for each Text Box on the Form, is created and enabled with the required Event and added into Object Array elements or to Collection Object or as Dictionary Object Items.

Assume that we have five text boxes on our Form.  Out of that three text boxes need some Event to run, to execute some task related to the data on that text box, like validation checks on data.  In that case we need three instance of the ClsText Class Module , one instance for each text box,  Other text boxes are left out.

When the enabled Event fires, from the Text Box on the Form, the related Class Object Array Element captures the Event and executes the Code within the Event Procedure. 

Last week we have added the Command Button control and it's Class Module along with Text Boxes for our demo runs.

New Demo Form: frmControls_All

In this demo Run we will include most of the frequently used controls on the Form..  The image of the demo Form is given below:

Besides Text Boxes and Command Buttons, we have Tab Control, Combo Boxes, List Boxes and Option Group controls.

We need new Class Module for each type of new controls on the Demo Form.  We already have Class Modules for Text Boxes and Command Buttons.

TextBox and CommandButton Class Modules.

Text Box and Command Button Class Module Code and their write-up is already posted in an earlier Article.  To reduce the size of this Post I will omit those details here.

You may visit directly to those areas of the Post, to view the VBA Code and their write-up, by selecting the Bookmark links given below:

  1. Class Module: ClsText
  2. Class Module: ClsCmdButton
  3. Download: You may download the Demo Database given there, run the sample Form and try out the controls on the From.  Explore the methods and the VBA Code implemented there.

Here, what we do is an extension of that with more controls on the Form.

Tab-Control Class Module: ClsTabCtrl

The Tab-Control Class Module: ClsTabCtrl VBA Code is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private WithEvents Tb As Access.TabControl

Public Property Get p_frm() As Access.Form
  Set p_frm = frm
End Property

Public Property Set p_frm(ByRef frmValue As Access.Form)
  Set frm = frmValue
End Property

Public Property Get p_Tb() As Access.TabControl
   Set p_Tb = Tb
End Property

Public Property Set p_Tb(ByRef tbValue As Access.TabControl)
  Set Tb = tbValue
End Property

Public Sub Tb_Change()
Dim msg As String, title As String

Select Case Tb
    Case 0
        frm.Controls("Computer").Value = Environ("ComputerName")
        title = Tb.Pages(0).Name
        msg = "Tab Page Index = 0"
    Case 1
        frm.Controls("UserName").Value = Environ("UserName")
        title = Tb.Pages(1).Name
        msg = "Tab Page Index = 1"
End Select

MsgBox msg, , title

End Sub

The ClsTabCtrl Class Module declares Access.Form and Access.Tab-Control Properties in Objects frm and Tb respectively.  The Tab Control property is declared with the Key word WithEvents to capture the Events taking place on the control.

The Set Property Procedures p_frm assigns the Form Object to the frm Property, declared with Private Scope.  The Get Property procedure provides the Form reference to address any other control on the Form, like frm.p_frm.Controls("UserName").value = Environ("UserName")

The next Set Property Procedures assigns the Tab Control Object to the Tb Property.  Similarly, the Get Property Procedures provides the Tab-Control Object reference to the calling Program, as explained in the above paragraph.

Next, the Tab Page Click Event is captured in the Tb_Change() Sub-Routine and executes the Code there.  When the User Clicks on a particular Tab Control Page the Click Event is not fired instead  the Change Event takes place.  That is the reason why we have added the Tb_Change() Procedure.

Two string variables msg and title are declared to assign some string values to display in the  Message Box. When the Tab Page Click occurs the Tab Page Change Event fires and this is captured in the Tb_Change() sub-routine.  The sub-routine displays a message, indicating that the Event is  captured and executed the code in the sub-routine.

There are two Pages, on the Tab-Control, with Page index number 0 and 1.  There is a Text Box on each Page of the Tab Control.

When the Page Change Event fires we check for the Tab Page index number. If the first Page is clicked (with page index number 0), then the Text Box on that page will be updated with the Computer's Name.  Here, we use the frm Object reference to address the Text Box (with the name Computer)  on the first Tab Page and updates with the Computer's name.

The second page click will update the Text Box with the User Name (Window's UserName).

NB: The actual procedure you write on the Tb_Change() sub-routine depends on what you want to do on that Event in your Project.

The Combo Box Class Module: ClsCombo

The Class Module Code for Combo Box control is given below:

Option Compare Database
Option Explicit

Private WithEvents cbx As Access.ComboBox

Public Property Get p_cbx() As Access.ComboBox
 Set p_cbx = cbx
End Property

Public Property Set p_cbx(ByRef cbNewValue As Access.ComboBox)
 Set cbx = cbNewValue
End Property

Private Sub cbx_Click()
Dim vVal As Variant
Dim cboName As String

vVal = cbx.Value
cboName = cbx.Name

Select Case cbx
    Case "Combo10"
        'Code Goes here
    Case "Combo12"
        'Code Goes here
End Select

MsgBox "Clicked: " & vVal, , cboName
End Sub

The Class Module for Combo Box declares a single Property cbx Object, with Private Scope and with the key word WithEvents to capture the Events fired from the Combo Box.

The Public Property Procedure Set p_cbx() receives the Combo Box control reference from the Form and assigns it to the Property cbx.

The Get Property Procedure passes the reference of the control to the calling program outside this module.

The next sub-routine cbx_Click() captures the Click Event of the Combo Box on the Form. The Select Case structure tests for the source of the Click Event, fired from which Combo Box and accordingly the Code executes under that combo box name. 

If you would like to capture any other Events, like OnGotFocus, OnLostFocus, AfterUpdate, OnChange etc. you can write sub-routines in the same Class Module, like cbx_GotFocus() and write the required VBA Code.  Need to enable the Event in the Derived Class Module to fire the Event as and when it occurs on the Form.

The Click Event sub-routine displays a common message, with the item value selected from the Combo Box.

The List Box Class Module: ClsListBox

The List Box Class Module Code structure is similar to the Combo box Code and programmed to capture only the Click Event.

Option Compare Database
Option Explicit

Private WithEvents LstBox As Access.ListBox

Public Property Get p_LstBox() As Access.ListBox
 Set p_LstBox = LstBox
End Property

Public Property Set p_LstBox(ByRef pNewValue As Access.ListBox)
 Set LstBox = pNewValue
End Property

Private Sub LstBox_Click()
Dim vVal As Variant
Dim lst As String

vVal = LstBox.Value
lst = LstBox.Name

Select Case lst
    Case "List16"
    Case "List18"
End Select

  MsgBox "Clicked: " & vVal, , lst

End Sub

It can be modified with additional sub-routines to capture any other Event Raised on the List Box.  The existing Code works on similar lines of the Combo Box and displays the selected item value in the message box.

The Class Module for Option Group: ClsOption

The Option Group have three different styles: 1. Option Buttons, 2. Check Boxes, 3. Toggle Buttons.  In our Demo Form we have used only two styles: Option Buttons and Check Boxes.  But, all the three works the same way only the display style is different and their Control names start with the name Frame followed by a number, like any other control on the Form: Frame18, Text2, Combo10, List12 etc.

The ClsOption Class Module Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Opts As Access.OptionGroup

Public Property Get p_Opts() As Access.OptionGroup
  Set Opts = Opts
End Property

Public Property Set p_Opts(ByRef pNewValue As Access.OptionGroup)
  Set Opts = pNewValue
End Property

Private Sub Opts_Click()
Dim txtName As String, intVal As Integer
Dim msg As String, strVal As String

intVal = Opts.Value
strVal = Opts.Name

Select Case strVal
    Case "Frame25"
        Select Case intVal
            Case 1
            Case 2
            Case 3
        End Select
    Case "Frame34"
        Select Case intVal
            Case 1
            Case 2
            Case 3
        End Select
End Select

msg = msg & " Click :" & intVal

MsgBox msg, , Opts.Name

End Sub

Option Group Items have labels that gives their actual purpose and meaning but all items have index numbers starting with 1.  In the Click Event Procedure we check for the Item index number to determine what to do, like Open a Form or Display Report or Run a Macro or whatever you want to do. 

It always fires the Frame Events and we check for the selected index number.to run a related action.

Now, the Class Modules for all the controls on the Demo Form is ready. 

Note:  There may be controls on the Form which are not enabled with any Event and doesn't fire any Event.  In those cases we don't create the Class Module instances for them.  But, we may read or update those control's values from other Control's Class Module instances. 

Example: We have two Text Boxes on the Tab Control Pages.  These Text Boxes are updated with Computer Name and Windows User Name, from the Tab Control's Class Module.

Now, we have all the sample Class Modules (let us call them the Class Module Templates) for all type of Controls on the Form. Depending on the requirement of a particular Form in your Project create a copy of the required Class Module Templates and customize their existing sub-routine or add new ones to capture required Events and run the related Code.

The Derived Class Module: ClsControls_All

We need an intermediary Class Module, between the stand alone Class Modules of each type of controls and the Form's Class Module,  to organize the Class Modules for the Controls on the Form and  to enable the required Events for them.

We will create a Derived Class Module with all type of controls' Class Modules as it's Properties.  The required Events will be enabled by testing their control names on the Form.

The Derived Class Module ClsControls_All Code is given below:

Option Compare Database
Option Explicit

Private tx As ClsText
Private cmd As ClsCmdButton
Private cbo As Clscombo
Private lst As ClsListBox
Private opt As ClsOption
Private tbc As ClsTabCtrl
Private Coll As Collection
Private fom As Access.Form

Public Property Get p_fom() As Access.Form
    Set p_fom = fom
End Property

Public Property Set p_fom(ByRef objFrm As Access.Form)
    Set fom = objFrm
End Property

Private Sub Class_init()
Dim ctl As Control
Const Evented = "[Event Procedure]"

Set Coll = New Collection

For Each ctl In fom.Controls 'check through Form controls

    Select Case TypeName(ctl) 'pick only the required control type
           Case "TextBox"
                Select Case ctl.Name
                    Case "Text2", "Text4", "Text6"
                        Set tx = New ClsText 'create new instance
                        Set tx.p_frm = fom 'assign Form Object to property
                        Set tx.p_txt = ctl 'assign control to p_txt Property
                        tx.p_txt.AfterUpdate = Evented 'enable AfterUpdate Event
                        tx.p_txt.OnLostFocus = Evented 'enable LostFocus Event
                'Add ClsText Object instance tx to Collection
                        Coll.Add tx
                'Release ClsText Object tx from memory
                        Set tx = Nothing
                End Select
            Case "TabControl"
                Set tbc = New ClsTabCtrl 'create instance of ClsTabCtrl
                Set tbc.p_frm = fom 'pass Form Object to p_frm Property
                Set tbc.p_Tb = ctl 'pass Tab Control to p_Tb Property
                tbc.p_Tb.OnChange = Evented 'enable OnChange Event
            'Add ClsTabCtrl instance tbc to Collection Object
                    Coll.Add tbc
            'Release tbc instance from memory
                Set tbc = Nothing
            Case "CommandButton"
                Select Case ctl.Name
                    Case "Command8", "Command9"
                       Set cmd = New ClsCmdButton 'create new instance of ClsCmdButton
                       Set cmd.p_Btn = ctl ' pass Command Button control to p_Btn Property
                       cmd.p_Btn.OnClick = Evented 'enable OnClick Event
            'Add ClsCmdButton instance cmd to Collection Object
                      Coll.Add cmd
            'Release cmd instance from memory
                       Set cmd = Nothing
                End Select
            Case "ComboBox"
                Select Case ctl.Name
                    Case "Combo10", "Combo12"
                        Set cbo = New Clscombo ' create new instance of ClsCombo Class
                        Set cbo.p_cbx = ctl ' pass control (Combo10 or Combo12) to CB Property
                        cbo.p_cbx.OnClick = Evented ' enable OnClick Event
            'Add ClsCombo instance cbo to Collection Object
                       Coll.Add cbo
            'Release cbo instance from memory
                      Set cbo = Nothing
                End Select
            Case "ListBox"
                Select Case ctl.Name
                    Case "List14", "List16"
                      Set lst = New ClsListBox ' create new instance of ClsListBox
                        Set lst.p_LstBox = ctl ' pass the control to lst.LB Property of instance.
                        lst.p_LstBox.OnClick = Evented ' enable OnClick Event
            'Add lst instance to Collection Object
                       Coll.Add lst
            'Release lst instance from memory
                      Set lst = Nothing
                End Select
            Case "OptionGroup"
                Select Case ctl.Name
                    Case "Frame25", "Frame34"
                      Set opt = New ClsOption ' create new instance
                        Set opt.p_Opts = ctl  ' pass control to opt.OB Property
                        opt.p_Opts.OnClick = Evented ' enable OnClick Event
            'Add opt instance to Collection Object
                       Coll.Add opt
            'Release lst instance from memory
                      Set opt = Nothing
                End Select
    End Select

End Sub

On the Global declaration area of Class Module ClsControls_All  we have added Class Module of all controls on the Form as Objects with Private Scope.  We have declared a Collection Object and a Form Object as well.

With our earlier trial run experience we have learned that the Collection Object is the easiest and better option than the Class Object instance Arrays.  The Array method  needs separate index counters for each type of control Class Module Objects.  For every new instance of an Object we have to increment the index counters, re-dimension the Array for new Element and so on.

Adding each instance of different type of Control's Class Module to Collection Object is easier and alleviates the need for all the extra steps  mentioned above. 

The Set Property Procedures assigns the Form Object, passed from the active Form, to the fom Property. 

The Class_Init() sub-routine is called from the Set property procedure to enable the Events on each required control on the Form, so that when the Event fires it is captured in their Class Module Sub-Routines.

The Get Property procedure services the outside request for the Form Object. 

The Class_init() sub-routine declares a Control Object ctl and a string constant Evented.

The Collection Object is instantiated as the Object Coll.

The controls on Form, like Text Box, Tab Control etc. will be enabled with required Events, like AfterUpdate, LostFocus, Click or any other on those control's Class Module Object and then add those instances to the Collection Object Item

Remember, the Form Control Object Property, like Text Box was declared with the WithEvents key word, enabling it to capture the Event, when it occurs on the Controls on the Form.  When those Event occurs it is captured in it's Class Module Object instance in the Collection Object and executes the sub-routine code, related to that event.

The For Each . . .Next Loop takes each control on the Form and tests whether it is the required type, like TextBox, TabControl, ComboBox and others.  Controls like Labels, images, activex controls etc., if any, are ignored.

Further the control Name check is performed, within a particular Type of Controls, to enable the required Event for that object.  First we check for the Text Box controls with the names Text2, Text4, Text6. When one of these Text Box control is found the ClsText Class Module is instantiated as tx Object.  The Form object fom is passed to the tx.p_frm property of  tx object. The tx.p_txt  object property is assigned with the Text Control ctl.

In the next step Text2 Text Box is enabled with the AfterUpdate and LostFocus Events. After these initialization steps the ClsText Class Object tx is added to Collection Object Item.

The same process is repeated for Text4 and Text6.  If each Text Box needs different Event to be enabled then they must be put under different Case statements and enable the required Event and add a new instance of the Class Object to the Collection Object.

Since, all the three Text Boxes are enabled with the same AfterUpdate and OnLostFocus Events all their names are put within the same Case statement. 

Note:  There are two more Text Boxes, one each on both Tab Pages.  Even though they are part of the Form controls we have not enabled them with any Events.  They will be used for displaying some values during the execution of Tab Page Change() Event procedure.

The Tab Control's Page Click action runs the Change Event, not Click Event.  By default the first TabCtl18.pages(0) will be current.  When you click on the second TabCtl18.pages(1) the Text Box (with the name UserName) will be updated with the Windows User Name.  When the first tab page is clicked the Text Box on it will be updated with the Computer's Name, with the statement frm.Controls("Computer").Value = Environ("ComputerName") in the ClsTabCtrl.  To address these text box controls directly we have added a Form object frm Property to the Class Module ClsTabCtrl.

All other controls on the Form, Command Buttons,  Combo Boxes, List Boxes and Option Group are enabled with the Click Event only.  Their Class Modules have only Click Event Sub-Routines to capture and display a message for demo purposes. 

If you would like to capture some other Event from a control then add the sub-routine for that event in it's Class Module and enable it in the Derived Class Module ClsControls_All.

The Form: frmControls_All's Class Module Code

Option Compare Database
Option Explicit

Private A As New ClsControls_All

Private Sub Form_Load()
    Set A.p_fom = Me
End Sub

The Derived Class Object ClsControls_All is declared and instantiated as Object A.

The current Form Object is passed to the A.p_fom Property Procedure as it's parameter.  That is the only Code required on the Form's Class Module.

The Demo Run

Download the Demo database from the download-link given at the end of this Article.

When you open the Demo Database the Form frmControlls_All opens in normal View, by default.

Testing Text Box – AfterUpdate, LostFocus Events

Tap on the Tab Key when the insertion point is on the first Text Box, to fire the LostFocus Event.  The Text Box will be inserted with the Text: msaccesstips.com.  This method is good for inserting some default text, if the data field rule is 'not to leave the Text Field empty'.

Make some changes to the text, by adding/removing some text, and then press Tab Key again.  This time the AfterUpdate Event fires and a message box is displayed with the changed text.

The Next two Text Boxes also responds to these Events similarly.

When you press the Tab Key in the Text Box the default text msaccesstips.com is inserted, only when you leave it empty.  If you type some value into the Text Box and press Tab Key then both AfterUpdate and LostFocus Event fires one after other.  But the lostfocus Event will be silent.

Testing Tab Control Page Click Event

By default the first Tab Control Page will be the active page.  Click on the second Tab Control Page.  The Change Event fires and the Text Box on the page is updated with the Windows User Name.

Click on the first Tab Page.  The text box on the first tab page is updated with the Computer Name.

Command  Button Click Event.

Click on the top Command Button.  This will open Form1 displaying some text, with hyperlinks to this Website.

The second Command Button Click displays a message from the ClsText Class Module instance Item from the Collection Object.

Click Events of ComboBox, ListBox, Option Group

All these controls, on the Form frmControls_All , are enabled with the Click Event through the Derived Class Module ClsControls_All and clicking on them will display the selected item in a Message Box.

All the above Class Module based Event enabled Sub-Routine Code is for demonstration of the programming approach only. 

We have developed systematic and customizable Class Module Templates to make VBA Coding much easier than before.  When you start working on a new Project you can make a copy of this Class Module templates and customize it quickly, as per the current project's requirement.  It is easier to debug the code and you know where to look for issues that you may encounter on the field testing stage or while debugging logical errors of your Project.

The Functional Diagram

But, before that if you have not properly understood the intricacies or the arrangement of all the pieces of the puzzle and how they are all related each other in their functions then take a look closely at the diagram below.

I suggest you better take a second look from the beginning Pages of this Series.  The links are given at the bottom of this page. 

If you have a ready to use Access Database then make a copy of it and try to restructure the coding based on what you have learned here.  You will know the difference, when it becomes better organized and easily manageable.

Demo Database

You may download the demo database from the link given below and try out the Form as explained above.

Links to WithEvents ...Tutorials.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


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