Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Reusing Form Module VBA Code for New Projects.

Streamlining Form Module Event Procedures.

The Existing Form Module Coding Procedure.

Access Forms incorporate a variety of controls, including Textboxes and Command Buttons, each serving specific functions. Typically, we create numerous Event Procedures in the Form's Class Module to execute various tasks related to these controls. However, a common challenge arises when handling multiple Event Subroutines for a single TextBox. The code tends to be dispersed, lacking organization, and intermixed with Event Procedure Codes for other controls in the Form Module.

This often results in a less-than-optimal development experience, requiring us to repeatedly navigate to the Form in design view. Whether making user interface design adjustments or refining code in the Form Module, we find ourselves frequently accessing the Form to locate and modify specific Event Procedure Code through its associated Event related Property.

The Streamlining of  Class Module Code.

This topic is intricate, demanding your careful attention to the series of examples provided on these pages. It’s essential to grasp the concept and procedures thoroughly by experimenting with them firsthand before advancing to the next level.

For those unfamiliar with Microsoft Access Class Module and constructing Access Class Objects, it’s recommended to review the earlier blog posts, starting from the MS Access Class Module and VBA series of articles. These initial articles were designed for beginners to grasp the fundamentals of building Class Module Objects through tutorials. Links to these earlier articles are provided at the end of this page for your convenience.

This topic will be explored through a series of articles distributed over several weeks, aiming to elucidate the fundamentals of this intricate concept through practical examples. Readers can expect to find external Class Module VBA code samples, event flow diagrams, and downloadable demo databases with ready-to-run examples in the upcoming weeks.

Control's Event Procedures on the Form. 

But, let us try to understand a few things we take for granted, like writing Event Procedures for controls on the Form.  When we want to do some task, like check the validity of the data in the Text Box we take the OnExit or BeforeUpdate Event Property and use one of the three following choices:

1. Gives the name of a Macro in the Event Property to run the Macro Code when the Event (like BeforeUpdate) takes place.

2. Call a Public Function from the Event Property to run.

3. Or write an Event Procedure in the Form Module, like BeforeUpdate(), to execute the Code when the Event is fired. 

If one of the first two options is used, then the Form doesn't need a Class Module. But, when the [Event Procedure] option is selected, the MS Access System automatically adds a Class Module to the Form.

It’s fascinating to understand how the system triggers the event action from the form’s controls, captures it within the form’s Class Module, and executes the VBA code tailored for that control’s specific task.

All Access objects or controls, such as TextBoxes, ComboBoxes, ListBoxes, and others, are structured as objects within standalone Class Modules. Each of these objects possesses properties that determine their appearance, colors, and inherent events. 

When we select a TextBox control from the menu and position it as desired, Access generates it with a default name, such as Text0. However, we have the option to replace this default name with a more descriptive one. The TextBox we’ve added to the form is essentially a copy (instance) of the Access.TextBox Class. By selecting the [Event Procedure] option in the BeforeUpdate Event Property, we trigger the event (using the RaiseEvent action) and subsequently write the event subroutine code within the empty event subroutine stub automatically provided by the Access System. Notably, the control’s name is consistently prefixed in the event procedure subroutine name, like so:

 Sub Quantity_BeforeUpdate()
 End Sub

In the realm of Access Objects designed with standalone Class Modules, they inherently possess their own set of built-in Events. While the inner workings of how Microsoft Access manages these events for controls on a Form may not be explicitly transparent, a notable observation lies in the configuration of the On LostFocus Event Property.

When this String Data Type property is set with the text [Event Procedure], a sequence unfolds: the Object's Event Announcer activates, announcing the event (RaiseEvent). Simultaneously, the Object Module Listener (utilizing WithEvents) captures this announcement. Consequently, an empty subroutine stub emerges in the Parent Form Module, resembling 'Sub Quantity_LostFocus()', awaiting the VBA Code to be seamlessly integrated within this empty procedural framework. This methodology facilitates a structured approach to incorporating code within the designated Event Procedure stub, triggered by the LostFocus event.

Have you ever thought of finding out how the inbuilt Events are defined and what it does to fire an Event to execute a  small block of VBA Code to do some task?

Let's delve into a straightforward example to grasp how object events are defined, raised, and captured to write code in the form module and execute the assigned task. Over the upcoming weeks, we'll extensively explore this topic of event firing and capturing in various contexts. Our goal is to introduce a fresh approach to VBA coding within the standalone class module, enabling swift and seamless coding. Furthermore, this approach facilitates the export and reuse of VBA code segments for other projects, thereby reducing database development time.

User-defined Custom Events.

Key Words: Event, RaiseEvent, and WithEvents.

  1. Open your Database.

  2. Create a new Form.

  3. Insert a Textbox.

  4. Display the Property Sheet of the Textbox.

  5. Change the Name Property value to Msg.

  6. Select the On Change Event Property and select [Event Procedure] from the drop-down list.

  7. Click on the build (. . .) Button to open the Form Module. 

  8. Copy and Paste the following VBA Code into the Form Module:

    'Define user-defined Event Message
    Public Event Message(txt As String)
    Private Sub Msg_Change()
    'Announce/Transmit the Event
        RaiseEvent Message(Me!Msg.Text)
    End Sub

    The initial declaration statement in the VBA code above introduces a user-defined event named Message(), accompanied by a single parameter of String type to be passed when invoked. The event must be defined with a public scope, followed by the keyword Event, and then the event name (not containing the underscore character, such as txt_Message), along with any parameter list enclosed in parentheses.

    In the Change Event Procedure of the Msg TextBox, we trigger the Message Event Procedure using the statement RaiseEvent Message(Me.Msg.Text). By running the Event within the Change Event Procedure, each time a character is typed in the TextBox on the form, the Event is triggered. We’ll capture this action in another Form Module and display the TextBox contents there to ensure that our User-defined Event is functioning correctly.

  9. Change the Text Box's child Label Caption value to Msg:.

  10. Save the Form with the name Form1 and close the Form.

  11. Create a new Form with the Name Form2 and open it in Design View.

  12. Change the size of the form to as small as Form1.

  13. Insert a Label control on the Form, and enter some text in the label's Caption to prevent Access from removing the Label Control from the Form.

  14. Change the Popup property value of the Form to Yes

  15. Select the Form Load Event Property and select [Event Procedure] and click on the build (. . .) Button to open the Form Module.

  16. Copy and Paste the following VBA Code into the Form2 Module overwriting the existing Code Lines:

    Option Compare Database
    Option Explicit
    'Declare the listener Form1 Class Object with the name frm.
    Private WithEvents frm As Form_Form1  
    Private Sub Form_Load()
    On Error Resume Next
        Set frm = Forms("Form1") 'assign open Form Form1 object
    End Sub
    'Execute frm_Message Event, with Listener frm object as prefix
    Private Sub frm_Message(str As String) 
        Me.Label0.Caption = str
    End Sub

    Check the line with the WithEvents keyword. This declaration line establishes a Form object named frm and assigns a reference to Form1’s Class Module, with the prefix Form_ (Form_Form1). In VBA or elsewhere, you cannot reference a form in this manner if the form lacks a Class Module.

    The WithEvents keyword is known as a Listener (like a Radio Receiver) of Events that takes place on Form1.

    Merely defining a form object with the WithEvents keyword, similar to a Dim statement, is not sufficient. We must initialise the frm object variable with the active Form1 (the RaiseEvent - Transmitter) object reference in memory.

    In the Form_Load() Event Procedure, we accomplish this with the statement Set frm = Forms(“Form1”). However, if Form2 is opened before Form1, it will result in an error. To circumvent this issue, we’ve included an error trap line to ignore the error and proceed with the subsequent line of code.

    The next Subroutine is our actual user-defined event's action-packed Code.

    Every time we type a character in the text box on Form1; this will immediately appear on the Label Control on Form2.

    The user-defined Event Message() will be fired every time we type a character in the Text box on Form1 and will be captured in Form2 and displayed in the Label Control.

    Note: Now, the Form_Form1 Module is a complete Object like a TextBox, with all the three Event Firing and Capturing mechanisms: Event, RaiseEvent, and the WithEvents (Event Capturing ability) added when Instatiated in Form2 Class Module. The Event Procedure Code must be written on the parent module (Form2 Module) of the Form1 Object Instance.

  17. Save and Close Form2. Close Form1, if it is kept open.  Let us test our user-defined Event Message().

  18. Open Form1 in Normal View.

  19. Open Form2 in Normal View and drag it away from Form1.

  20. Type Hello World or anything you like in the TextBox on Form1.  The typed text should appear in the Label control on Form2, each character as you type them in the Text box.

Hope you understood the concept of how an Event is defined in Form1 and how it is invoked and captured in Form2 and executes the related Subroutine Code in Form2 Module to do some task. 

Note: On Form2, we monitor Form1 by establishing a reference to it in the frm object using the WithEvents keyword. When the Message Event is triggered (RaiseEvent) on Form1, the frm object associated with Form2 (a replica of the Form1 Module object) promptly captures it, and the corresponding subroutine prefixed with frm_ (Private Sub frm_Message()) is executed promptly.

You may try this or similar procedure with two different Forms to understand the relationship and logic of declaring Event, RaiseEvent, and WithEvents for capturing and Executing Event Procedure VBA Code.

Next week we will try how the RaiseEvent predefined Event of TextBox, like LostFocus is enabled at Run-time. 

Download the Demo Database.

    MS Access Class Module Object Lessons for Beginners.

  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 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 Transformation

Streamlining Form Module Code in Standalone Class Module.

  1. Reusing Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code - Part Two.
  3. Streamlining Form Module Code - Part Three
  4. Streamlining Form Module Code - Part Four
  5. Streamlining Form Module Code - Part Five
  6. Streamlining Form Module Code - Part Six
  7. Streamlining Form Module Code - Part Seven
  8. Streamlining Form Module Code - Part Eight
  9. Streamlining Form Module Code - Part Nine
  10. Streamlining Form Module Code - Part Ten
  11. Streamlining Form Module Code - Part Elevan
  12. Streamlining Report Module Code in Class Module
  13. Streamlining Module Code Report Line Hiding-13.
  14. Streamlining Form Module Code Part-14.
  15. Streamlining Custom Made Form Wizard-15.
  16. Streamlining VBA Custom Made Report Wizard-16.
  17. Streamlining VBA External Files List in Hyperlinks-17
  18. Streamlining Events VBA 3D Text Wizard-18
  19. Streamlining Events VBA RGB Color Wizard-19
  20. Streamlining Events Numbers to Words-20
  21. Access Users Group(Europe) Presentation-21
  22. The Event Firing Mechanism of MS Access-22
  23. One TextBox and Three Wrapper Class Instances-23
  24. Streamlining Code Synchronized Floating Popup Form-24
  25. Streamlining Code Compacting/Repair Database-25
  26. Streamlining Code Remainder Popup Form-26


1 comment:

  1. Hi, aprpillai, Thanks a lot for sharing this information. I didn't know nothing about this user defined events and how to use it. I'm going to follow all you have written about it in your block. ;-)


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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