Reusing Form Module VBA Code for New Projects. ~ LEARN MS-ACCESS TIPS AND TRICKS

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.

Open your Database.
Create a new Form.
Insert a Textbox.
Display the Property Sheet of the Textbox.
Change the Name Property value to Msg.
Select the On Change Event Property and select [Event Procedure] from the drop-down list.
Click on the build (. . .) Button to open the Form Module.
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.
Change the Text Box's child Label Caption value to Msg:.
Save the Form with the name Form1 and close the Form.
Create a new Form with the Name Form2 and open it in Design View.
Change the size of the form to as small as Form1.
Insert a Label control on the Form, and enter some text like Message or my text in the label's Caption.
Change the Popup property value of the Form to Yes
Select the Form Load Event Property and select [Event Procedure] and click on the build (. . .) Button to open the Form Module.
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.
Save and Close Form2. Close Form1, if it is kept open. Let us test our user-defined Event Message().
Open Form1 in Normal View.
Open Form2 in Normal View and drag it away from Form1.
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.

UserDefinedEvents.zip