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 are designed with several controls like Textboxes, Command Buttons, and others. We write several Event Procedures in the Form's Class Module for almost all the controls on the Form, for different tasks.  Different Event Subroutines are written for a single TextBox on the Form and all of them are scattered around in the Form Module among other controls' Event Procedure Codes, in an unorganised fashion. We open this Form in design view numerous times, either to make User Interface design changes or to add/refine the Code in the Form Module, reaching the Event Procedure Code through the Event Property.

After completion of this Project, we leave all the VBA codes with the Project and no part of it is useful for any other Project.  Besides that, the manual effort we put in to develop the Project is in no way useful to reduce the work of any other Project at all. 

We plan to streamline this whole procedure by taking away the VBA Code into the stand-alone Class Modules leaving the design task of the Form alone on the Form. By doing this we can reduce the manual effort we put into developing the Class Module Code and it can be exported to new Projects and modified to suit the purposes of the new Database.  

For modifying the Event Procedure Code we can go straight into the related stand-alone Class Module and make changes rather than struggling with the Form Design View and with its Control properties.

The Streamlining of  Class Module Code.

This is a very complex topic and needs your close attention to all the series of examples presented on these pages, to understand the concept, procedures, and try them out yourself to understand them fully before proceeding to the next level.

Those who are not familiar with Microsoft Access Class Module and building Access Class Objects can go through the earlier Blog Posts starting from MS Access Class Module and VBA series of Articles. The earlier Articles were meant for beginners to learn the fundamentals of Class Module Object building Tutorials. The earlier Article Links are given at the end of this Page if you would like to take a look at them.

This topic will be presented in a Series of Articles spread over several weeks exposing the fundamentals of this complex concept by demonstrating them by examples. External Class Module VBA Code Samples, Event flow diagrams, and Demo-database Downloads, with ready-to-run examples will be provided for readers in the coming 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 AfterUpdate 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 AfterUpdate) takes place.

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

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

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

It is interesting to know how the System invokes the Event action from the Form's controls, captures it in the Form's Class Module, and executes the VBA Code written for that Control's task.

All the Access Objects or Controls, like TextBox, ComboBox, ListBox, and others are designed as Objects with the Standalone Class Module.  All of these Objects have Properties, which give them shapes, Color, and with their own inbuilt Events.  

We select a TextBox Control from the menu, place it where we want it and Access creates it for us with a default name, like Text0. We can replace the default Name with a more meaningful name. The TextBox we placed on the Form is a  copy (Instance) of the Access.TextBox Class. We select the Option "[Event Procedure]", in the AfterUpdate Event Property, to fire the Event (Raise the Event) and write the Event Subroutine Code within the Empty Event Subroutine stub provided automatically by the Access System. The Control's name is always prefixed in the Event Procedure Subroutine Name, like:

 Sub Quantity_LostFocus()
 End Sub

Since; Access Objects are designed with the standalone Class Module they have their own built-in Events. We don't know exactly how Microsoft Access does this for the controls on the Form.  But, we know that when the On LostFocus Event Property is set with the text [Event Procedure], then the Object's Event Announcer is made to announce the Event (RaiseEvent) and the Object Module Listener (WithEvents) captures it and opens an empty subroutine stub in the Parent Form Module like 'Sub Quantity_LostFocus())', and we write VBA Code within the empty Procedure stub. 

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?

We will try a simple example to understand how the object Events are Defined,  Raised, and Captured and write Code in the Form Module, and execute the Code written for the task.  We will be discussing a lot about this single topic of Event firing and Event capturing in various ways in the coming weeks.  Our aim is to devise a new approach to VBA Coding in the stand-alone Class Module, rather than in the Form Module, so that we will be able to do the Coding quickly and effortlessly.  Moreover, we can export and use part of the VBA Code for other Projects, to reduce the 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 first declaration statement in the VBA Code above is a User-defined Event with the name Message() and has a single String Type Parameter to pass when called. The Event must be defined by Public scope followed by the keyword Event followed by the Event Name (Name should not contain the underscore character like txt_Message) and parameter list, if any, in parenthesis.

    Next, in the OnChange Event Procedure of the Msg TextBox, we generate the Message Event Procedure with the statement RaiseEvent Message(Me.Msg.Text). Since; we have run the Event on the OnChange Event Procedure, every time we type a Character in the Textbox on the Form the Event is fired.  We will capture this action in another Form and display the textbox contents there to see that our User-defined Event is working 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 like Message or my text in the label's Caption.

  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
    'Define Listener object frm, with reference to Class Module of Form1.
    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. On this declaration line, we are creating a Form object with the name frm and setting a reference to Form1's Class Module, with the prefix Form_ (Form_Form1). You cannot reference a Form like this in VBA or elsewhere if the Form doesn't have a Class Module.

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

    Defining a form object with the Keyword WithEvents alone (like a Dim statement) will not be enough, we have to initialize the frm object variable with the active Form1 (with the RaiseEvent - Transmitter) object in memory.

    In the Form_Load () Event Procedure we are doing that with the statement Set frm = Forms("Form1"). If Form2 is opened first, before opening Form1, then it will end up with an Error. To avoid that we have added an error trap line to ignore that error and continue with the next line.

    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 Form_Form1 Module is a complete Object like a TextBox, with all the three Event Firing and Capturing mechanism: Event, RaiseEvent and the WithEvents (Event Capturing ability) added when Instatiated it in Form2 Class Module. The Event Procedure Code must be written on the parent module of the Object.

  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 else you like.  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 to do some task. 

Note: On Form2 we are keeping a watch on Form1 by setting up a reference to it in the frm object with the Listener keyword WithEvents.  When the Message Event is fired (RaiseEvent) on Form1, the Form2-based frm object (a copy of the Form1 Module object) captures it immediately and the related Subroutine with the frm_ prefix (Private Sub frm_Message()) is executed immediately. 

You may try this or some other similar procedure with two different Forms to understand the relationship of declaring Event, RaiseEvent, and WithEvents. 

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. Re-using 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-Eleven.
  12. Streamlining Report Module Code in Class Module.
  13. Streamlining Report Module Code in Class Module-2.
  14. Streamlining Form Module Code Part-14:All Controls
  15. Streamlining Custom Made Form Wizard-15
  16. Streamlining Custom Report Wizard-16
  17. Streamlining Form VBA External File Browser-17
  18. Streamlining Event Procedures of 3D TextWizard-18


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