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

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

UserDefinedEvents.zip