Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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
Share:

Running Sum in MS-Access Query

Introduction.

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 SQL Code of RunningSumQ1Query and the records with Running-Sum values are given below.

SELECT Table_Units.ID, Table_Units.Units, RunningSum([ID],"ID","Units","RunningSumQ1") AS RunningSum
FROM Table_Units;

The recordset in datasheet view, with summary values in a separate Column, with the column name RunningSum, from where the RunningSum() Function is called.


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: Running_Sum:RunningSum([ID2],”ID2”,”[List Price]”,”RunningSumQ2”).

The RunningSum() Function VBA Code.

Option Compare Database
Option Explicit

'Declare a Generic Object
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 : Queries to generate Running Sum of a Column Value
'The Query can be used as source for other Processing needs.
'-----------------------------------------------------------
'Author  : a.p.r. pillai
'Date    : 1st Nov 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
Dim p As Variant

On Error GoTo RunningSum_Err

'If the Function is not called by the same Query
'then initialize Dictionary Object and Variables
If SumFldName <> fld 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")
    
    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
         rst.MoveNext
    Wend
    
    'close recordset and remove the database objects
    rst.Close
    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
    
   RunningSum = D(IKey)
Else
   '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.
   
   RunningSum = D(IKey)
End If

RunningSum_Exit:
Exit Function

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

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.

  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.

Three Static Variables are declared in the Function:

  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 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 Dictionary Object Item-Key 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 recordset is closed and the Database Object DB is cleared from memory.

Immediately after closing the Recordset the first record summary value is retrieved from Dictionary, using IKey Parameter, and returns it to the first record in RunningSum Column, by executing the statement RunningSum = D(IKey). All the above actions are taking place when the  control Variable K is equal to 1.

Subsequent Calls of the function with the [ID2] 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.


CLASS MODULE

  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

COLLECTION OBJECT

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

DICTIONARY 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

MS-ACCESS EVENT HANDLING

  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
Share:

Date2Text and Text2Date Functions

Introduction.

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

D=Cdate("22/10/2019")

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

D=DateValue("11/10/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.

Example:

? 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
Share:

Call Function From MouseMove Event Property

Introduction.

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
Next

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
Share:

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.

Syntax:

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

On Error GoTo ROUNDDOWN_Err
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

ROUNDDOWN_Exit:
Exit Function

ROUNDDOWN_Err:
MsgBox Err & " : " & Err.Description, , "ROUNDDOWN()"
Resume ROUNDDOWN_Exit
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.

FormulaDescriptionResult
=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. Rounding Function MRound of Excel
  2. Microsoft Excel Power in MS-Access
  3. Roundup Function of Excel in MS-Access
  4. Proper Function of Excel in Microsoft Access
  5. Printing MS-Access Report from Excel
  6. Opening Excel Database Directly
Share:

WithEvents and All Form Control Types

Introduction

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"
        'Code
    Case "List18"
        'Code
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
                'code
            Case 2
                'Code
            Case 3
                'Code
        End Select
    Case "Frame34"
        Select Case intVal
            Case 1
                'Code
            Case 2
                'Code
            Case 3
                'Code
        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
    Class_init
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
    
Next

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
Share:

WithEvents Textbox CommandButton Dictionary

Continued from Last Week's Post

So far we have used the Collection Object , to hold all the Class Object instances of the Form Controls as an alternative to Class Object Arrays.

If you have directly landed on this Page then please go through last Week's Post: WithEvents TextBox and CommandButton Control Arrays and then continue on this page.

Last week we have used Class Object Arrays of Text Box and Command Button Controls on the Form.

The difficulty of this method was to maintain separate Array Indexes for each type of control's Class Object instances, besides re-dimensioning each Array element every time.  When there are several type of Form Controls, like Combo Boxes, List Boxes, Option Groups, Tab Control and others this approach will grow into a very complex situation.  Earlier, we were able to alleviate this complexity by using Collection Object, as the container of all the controls Class Object Instances.

We have tried few examples with Collection Object in some of our earlier Post.  Links of all the Posts, on Form and Report Control's Event Capturing topic, are given at the end of this Post for your reference.

Usage of Dictionary Object, Replacing Array

Here, we are going to use the Dictionary Object, instead of Collection Object, to hold all the Form Controls' Class Object Instances.  Let us see how it works and what it takes to implement the Dictionary method.

Here, I will bring in Last week's Derived Class Object (ClsTxtBtn_Derived)  Code without any change.  The full VBA Code of last week's Class Module Derived Object Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The ClsText and ClsCmdButton Classes are defined as Array Objects T() & B() Properties, with an un-declared number of array elements.  The Form Property m_Frm declared as third item followed by the Get/Set Property Procedures for the Form Object. 

After assigning the Form Object in the Set Property Procedure the Class_Init() Sub-Routine is called to enable the required Form controls' Event Procedures.

Three Text Boxes (Text2, Text4, Text6) are enabled with the AfterUpdate() and LostFocus() Events only, because we didn't set up any other Sub-Routine in the Class Module ClsText to capture other Events from the Text Boxes.

Note: Even if we have sub-routine code for, say BeforeUpdate,  GotFocus, KeyDown, KeyUp etc., in the ClsText Class Module we may not use all of them for a particular Project.  Whatever Event we need to capture in a particular Form will only be enabled in the Derived Class Module.  Other sub-routines will remain in the Class Module till we encounter a need for them in a particular Form or Report.

The Form and TextBox control references are passed from the Derived Object to the ClsText Class Object Properties through the following statements:

Set T(tCount).p_frm = m_Frm
Set T(tCount).p_txt = ctl

Command Button Control reference is passed to the ClsCmdButton Class Module.  The Command Button Click Event is enabled.

This procedure repeated for each Control on the Form in the Derived Class Object Module: ClsTxtBtn_Derived.

Logical Error in VBA Code

The ClsTxtBtn_Derived Class Module Code works perfectly for our earlier example.  But, there is a logical error in the code and we will correct it in the later versions of the Code. 

The placement of the following lines of Code under the Case "TextBox are not in the correct location to place, because there is a chance that the Class Module Object will occupy more memory space.

Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control

The issue with the above code placement is that if there are some extra text boxes on the Form, without any Event enabled on them, even then the Class Object will be instantiated for those cases also and added to the Array,  occupying extra memory space.  It will happen silently without any side effects.

The above code lines must be placed immediately below all the Case statements Case "Text2", Case "Text4" and Case "Text6" structures for the correction of the logical error.  This change is required for the Command Button Case statements also.  If all the Text Boxes and Command Buttons on the Form are enabled with some Event then no need for any change.

In all our demo Forms we have introduced two or three Text Boxes or few Command Buttons and enabled some Event on all of them.  In those situations the above Code placement is correct and intentionally made that logical position to keep the code simplified, avoiding duplication of code, under each Case Statement..

The Dictionary Object

If you have not come across the Dictionary Object and it's usage so far then please visit the following links or at least the first two:

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

By Comparing Collection Object with Dictionary, both have the Add method and takes two parameter values: ItemKey and Item.

Dictionary Object Syntax: Object.Add ItemKey, Item – both values mandatory.

Collection Object  Syntax:  Object.Add Item, ItemKey – second parameter  optional.

Unlike Dictionary Object the ItemKey is second parameter in Collection Object and is optional. Since,  all the control names on the Form are unique it can be used as Dictionary Object ItemKey.

Dictionary Object is part of Windows Scripting Language and need to create an Object in VBA with the following statement:

Dim D as Object
Set D = CreateObject("Scripting.Dictionary")

Alternatively you can select and add the Microsoft Scripting Runtime Library File to your Project from Tools - - > References Library List.  After that you can declare Dictionary Object as shown below:

Dim D as Dictionary

If you do this then it has an added advantage of displaying it's list of Properties and Methods, when you type the declared Object name followed by a dot ( say D.)

The new derived Class Module ClsTxtBtn_Dictionary VBA Code is given belowSo far we have used Collection Object as an alternative to Class Object Instance Arrays.  Here we will learn the usage of Dictionary Object as container of Class Object Instances (of TextBox and Command Button).

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form

Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance Case "Text4" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing Case "Text6" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing End Select Case "CommandButton" Select Case ctl.Name Case "Command8" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance Case "Command9" Set B = New ClsCmdButton Set B.p_Btn = ctl B.p_Btn.OnClick = Evented 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B Set B = Nothing End Select End Select Next End Sub

Preparing for a Trial Run

  1. Create a new Class Module in the Demo database, you have downloaded from last week's Post, with the name ClsTxtBtn_Dictionary.
  2. Copy and Paste the above Code into the Class Module and save.
  3. Select Compile . . . from the Debug Menu to recompile the database and ensure that no errors encountered.
  4. Make a Copy of the Form frmClassArray with a new name frmClass_Dictionary.
  5. Open the Form frmClass_Dictionary in design view.
  6. Display the Form's Code Module and change the Code to match the Code lines given below:
    Option Compare Database
    Option Explicit
    
    Private A As New ClsTxtBtn_Dictionary
    
    Private Sub Form_Load()
       Set A.mfrm = Me
    End Sub
    
    
  7. Save the Form with the changed code.
  8. Open the Form in Normal View and try the Text Boxes and Command Buttons to test the LostFocus, AfterUpdate and Command Button Clicks works as before.

Revised Code Segments.

Since, all the three Text Boxes (Text2, Text4 and Text6) are enabled with the same set of Events (AfterUpdate & LostFocus) the Case statements can be clubbed into one line and can avoid duplication of Code as given below:

Select Case ctl.Name
            Case "Text2", "Text4", "Text6"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control

                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
        End Select

Similarly both the Command buttons have only one common Event, the Click Event.  Hence, their Code also can be combined into a single step, like the following code segment:

Select Case ctl.Name
            Case "Command8", "Command9"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
        End Select

The Revised ClsTxtBtn_Dictionary Code.

The full Class Module Code with the above change is given below:

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2", "Text4", "Text6" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance End Select Case "CommandButton" Select Case ctl.Name 'Both Command Buttons have only the same Click Event Case "Command8", "Command9" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance End Select End Select Next End Sub

You may create a new Class Module, Copy and Paste the above Code and save.  Change the Form Module Code to incorporate the new name of this Module and try out the Form controls, to test whether all of them works as before.

We have tried only with two type of controls, the Text Box and Command Button class Modules here.  How it will be when we have to deal with all type controls  Combo, List, Tab Control, Option Group on the Form.  All of them needs separate Class Modules, like ClsText and ClsCmdButton.  We will conclude this topic with one or two posts with almost all type of controls on the Form. 

If you have progressed through the earlier Posts (the links: No. 1 to 12) presented below then you will not find any difficulty in defining and setting up Class Modules for those controls mentioned above in a similar way, with Click Event enabled for each one of the yet to be tested form controls.


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
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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 msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering 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

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts