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 other controls, and write Event Procedures for several tasks in Form Class Module for almost all the controls on the Form.  We open this Form in design view numerous times, either to make design changes or to add or to refine the Code already written in the Form Module. After completion of the 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 become 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 modify them 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 these series of examples presented on these pages, to understand the concept, and 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 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. 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 action on the data of a Text Box we take the GotFocus or LostFocus or some other 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 when the Event (like LostFocus) takes place.

2. Call a Function from the Event Property.

3. Or write an Event Procedure for the Control in the Form Module, and the Event is generated by the Control (say a Textbox) on the Form.

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 and captures it in the Form's Class Module and executes the VBA Code written for the task.

All the Objects or Controls, like TextBox, ComboBox, ListBox, and others are designed with Class Module Objects.  All of these Objects have Properties, which give them shapes, and colors, and have their own built-in Events.  When the Control like Textbox instance on the Form Raises the Event (like LostFocus) this is captured on the Form Module and we write VBA Code for the task in the Form Module. The Control's name is always prefixed in the Event Procedure Subroutine declaration, like:

 Sub Quantity_LostFocus()
 End Sub

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

Do you ever think of finding out how the Events are Defined and what to do 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, Captured in Form Module, and executes 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 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 statement in the VBA Code above is a User-defined Event. 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 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 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.

  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 frm object with the Listener keyword WithEvents.  When the Message Event is fired (RaiseEvent) on Form1, Form2 based frm 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 to RaiseEvent of TextBox's predefined events like LostFocus (not user-defined) at Run-time. Capture the Event in our own user-defined Textbox Objects and run the Event Procedure on the same Form Module, as we normally do, but with our own Textbox object on behalf of the Textbox on the Form.

Download the Demo Database.

    MS Access Class Module Object.

  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


No comments:

Post a Comment

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 Android App 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