Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Streamlining Form Module Code Part Two

Understanding Form Controls Event.

I trust you found last week’s article and demonstration on user-defined custom events in the Form’s Class Module enjoyable. We delved into how events are defined, raised, or fired within the same Form Module, with the raised events subsequently captured by another open Form’s Class Module to execute the related custom event procedure code. I anticipate that you may have several questions regarding this topic of events, and I eagerly await exploring it further with you to provide additional insight into this technique.

Typically when designing an Access Form, we add various objects, such as TextBoxes, Command Buttons, and ComboBoxes, each with their own pre-defined Events and corresponding Event firing actions. These events are captured by their respective Event Procedures and execute VBA code to accomplish specific tasks.

In the past, we kept adding Access objects to the form used their inbuilt events, and wrote VBA Code without fully understanding how they are made to work.  Coding with whatever knowledge we attained through these processes in the form module, it become the second nature to continue doing that to get the task done.  I tried to delve a little deeper into the underlying mechanisms and tried to understand them, in the practical point of view, a little bit of the inner workings of these processes. 

When discussing Events on a form, it is important to keep the keywords Event, RaiseEvent, and WithEvents in mind. These Keywords are required to define an Event, invoke (or Fire, or RaiseEvent or Announce), and Capture the Event (WithEvents) in an Event Subroutine. These keywords are utilized in the following examples, as well as in forthcoming Articles.

  1. Defines an event in the Form1 Class Module using the keyword Event: Public Event QtyUpdate(). This is the conventional method for defining Access events.
  2. Fire the Event with the Keyword RaiseEvent: RaiseEvent QtyUpdate() in the Form1 Class Module.

  3. To capture the event in the Form2 Class Module, instantiate an object of the Form1 Class Module: Public WithEvents ofrm as Form_Form1. The user-defined event cannot be captured within the same form where it’s initially defined. This limitation arises because the Fired Event can only be captured in an instance of the Form Object declared with the WithEvents keyword.

    The resultant Event Procedure of ofrm_QtyUpdate() Event must be written in the target Form Module. The ofrm_ prefix to the Subroutine name is taken from the Event Capturing declaration: Private WithEvents ofrm As Form_Form1.

    Private Sub ofrm_QtyUpdate()
       'VBA Code
    End Sub

User-Defined Event Example-2.

To enhance comprehension of the mentioned keywords, let’s explore a new example involving user-defined events. Imagine we have a textbox enabling users to input a quantity with specific constraints. Our objective is to transmit the entered value to another form through a user-defined event for validation, and subsequently receive a relevant message in response.

To achieve this goal, we can define a Public Event named QtyUpdate() using the Event keyword. Subsequently, we can raise this event using the RaiseEvent keyword, passing the entered value as an argument. The event can then be captured in the class module of another open form using WithEvents, where the parameter value can be validated within an event procedure. Based on the validity of the parameter value, a suitable message can be displayed to the user.

To implement our events, let’s create two straightforward forms. In essence, we’ll define the events in the first form and execute the corresponding user-defined event procedure code in the second form’s class module.

The Event will be defined in the first form and it is fired from the first Form. The Raised Event in the first form is captured in the second Form's Class Module and executes the Event Subroutine Code. 

With this foundational understanding, our intention is to restructure the code typically written within the Form Module into separate Class Modules, thereby allowing the Form to focus solely on user-interface design. This strategic approach is anticipated to significantly streamline project development timelines.

Let us try something simple to understand the concept a little more.

  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: OrderQty (stands for Quantity).

  6. Change the child Label Caption value: Quantity  (1 - 5 only).

  7. Insert a Command Button below the Textbox and change its Name Property value to cmdClose.

  8. Change its Caption property to 'Close Form'.

  9. Add another Command Button below the earlier one and change its Name Property Value to cmdOpen.

  10. Change the Caption Property value to 'Open Event Capture Form'

  11. Display the Form's Property Sheet. Set the Record Selectors Property Value No.

  12. Set Navigation Buttons to No.

  13. Set Scroll Bars to Neither.

  14. Right-click on the TextBox and select Properties from the displayed Menu.

    Event Announcer Form
  15. Select AfterUpdate [Event Procedure] option on the TextBox Property and Click on the Build (. . .) Button to open the VBA Module.

  16. Copy and Paste the following VBA Code into the Form's Module, overwriting existing lines, if any:

    Option Compare Database
    Option Explicit
    
    'User-Defined Events
    'Captures in Second Form Module.
    
    Public Event QtyUpdate(mqty As Single)
    Public Event formClose(txt As String)
    
    Private Sub cmdOpen_Click()
        DoCmd.OpenForm "frmUDCapture", acNormal
    End Sub
    
    Private Sub OrderQty_AfterUpdate()
    
    'Announce the Event and pass the OrderQty as Prameter
      RaiseEvent QtyUpdate(Me!OrderQty)
      
    End Sub
    
    Private Sub cmdClose_Click()
    'Announce the Event and pass the Text as Parameter
      RaiseEvent formClose("Close the Form?")
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
      DoCmd.Close acForm, "frmUDCapture"
    End Sub
    
     
  17. Save the Form with the Name frmUserDefined.

User-Defined Events.

In the global declaration area of the form's class module, we have defined two user-defined events that closely resemble functions.

Public Event QtyUpdate(mqty As Single)
Public Event formClose(txt As String)

Access has built-in events that also look like functions, such as Text0_Exit(Cancel As Integer), Form_Unload(Cancel As Integer), and Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single).

The user-defined event declarations both begin with the keywords Public Event, followed by the event name and any associated parameters enclosed in parentheses. It is important to note that the event name should not contain an underscore character (e.g., Qty_Update). Additionally, the declaration must have a Public scope.

The first user-defined event is designed to validate the contents of the OrderQty textbox whenever it is updated on the form. The second event is triggered by the first command button to close the form and passes a text message as the parameter.

After entering a numeric value into the OrderQty TextBox and pressing the enter key, the OrderQty_AfterUpdate event procedure of the textbox is executed as usual. Within this event procedure, the RaiseEvent QtyUpdate(Me!OrderQty) statement is executed, which triggers the first user-defined event and passes the OrderQty textbox value as the parameter.

Private Sub OrderQty_AfterUpdate()
'RaiseEvent and pass the OrderQty as a parameter
  RaiseEvent QtyUpdate(Me!OrderQty)
  
End Sub

In Access, we typically write event procedures for objects such as TextBoxes and Command Buttons in the same form module. These objects are part of the Access system and have their built-in events and mechanisms for capturing those events. We write event procedures for these Objects in the Form's Class Module where they reside. Later, we will review how this process works and why it differs from the approach we take with user-defined events. You may have questions about these differences, and we will explore them in more detail shortly.

When you declare the Form1 object Instance with the WithEvents keyword in the form2 module, the Events Raised by the Form1 Module can be captured in the Event Subroutine written in the Form2 Module.  Instead of the Form1 Module, it can be from a Stand-alone Class Module too. This is because the WithEvents keyword allows the Form Module/Class Module to receive notifications about events raised by the Class object, and to respond to them by running the corresponding event procedures.

The user-defined events are not related to any object-based events like the built-in events of a TextBox. The only mechanism for raising a user-defined event is to explicitly call the RaiseEvent statement in the code, as we did in the example.

Additionally, a user-defined event declaration doesn't have any inherent mechanism of its own like a TextBox. It simply defines the event's name, any parameters, it may have, and its public accessibility. It's up to the programmer to implement the code that raises the event and the code that handles the event when it's raised.

It's also worth noting that a user-defined event can be raised only from the Class Module of a Form or from a stand-alone Class Module. However, as I mentioned earlier, in order to capture the event in another module, that module must use the WithEvents keyword, to declare an Instance of the Class Module of the first Form. 

The RaiseEvent statement within the OrderQty_AfterUpdate() event procedure announces the QtyUpdate() user-defined event and passes the OrderQty value as a parameter. This QtyUpdate() event is then captured in the second form's module, the Event Procedure is executed, and validates the OrderQty value.

The cmdClose_Click() event procedure in the frmUserDefined Form raises the formClose() event with a message text as the parameter. Then, the second Form's Module captures this event and shows the message text to the User to determine whether to close the frmUserDefined Form or not, based on the User's responses.

This is a simple example of how user-defined events can be used to communicate between different Forms or Class Modules in VBA.

Private Sub cmdClose_Click()
'Announce the Event and pass the Text as Parameter
  RaiseEvent formClose("Close the Form?")
End Sub

Private Sub Form_Unload(Cancel As Integer)
  DoCmd.Close acForm, "frmUDCapture"
End Sub

The Form_Unload() event of the frmUserDefined form is triggered first, as the form is closed, and the frmUDCapture Form is closed first. 

 The Form_Close() event is triggered next after the frmUDCapture form is closed and closes the frmUserDefined Form in the cmdClose_Click() event procedure. 

The User-Defined Event Capturing Form.

  1. Create a new Form and open it in Design View.

  2. Insert two labels as shown in the Image of the Form's design view given below.

    Event Capturing Form
  3. Change the first label caption to EVENT MESSAGES.

  4. Select the second label and display its Property Sheet.

  5. Change the Name Property Value to Label2, if it is different.

  6. Change the Caption Property Value to Event.

  7. Display the Form's Property Sheet. Set the Record Selectors Property Value: No.

  8. Set Navigation Buttons to No.

  9. Set Scroll Bars to Neither.

  10. Change the Detail Section Area of the Form as small as shown in the above Image.

  11. Display the Form's VBA Module.

  12. Copy the following VBA Code and Paste it over the existing lines, if any, in the Module.

    Option Compare Database
    Option Explicit
    
    Public WithEvents ofrm As Form_frmUserDefined
    
    Private Sub Form_Load()
    On Error Resume Next
    
    Set ofrm = Forms("frmUserDefined")
    End Sub Private Sub ofrm_QtyUpdate(sQty As Single) Dim Msg As String If Nz(sQty, 0) < 1 Or Nz(sQty, 0) > 5 Then Msg = "Valid Qty Range: 1 - 5 Only" Else Msg = "Order Qty: " & sQty & " Approved." End If Me.Label2.Caption = Msg MsgBox Msg, vbInformation, "QtyUpdate()" End Sub Private Sub ofrm_formClose(txt As String) Me.Label2.Caption = txt If MsgBox(txt, vbYesNo, "FormClose()") = vbYes Then DoCmd.Close acForm, ofrm.Name Else Me.Label2.Caption = "Close Action = Cancelled." End If End Sub
  13. Save the Form with the Name frmUDCapture and Close it.

Event Capturing Form Module VBA Code Review.

Public WithEvents ofrm As Form_frmUserDefined

Private Sub Form_Load()
	On Error Resume Next
	Set ofrm = Forms("frmUserDefined")
End Sub

  1. The line of code Public WithEvents ofrm As Form_frmUserDefined is a crucial global declaration in the second Form's Class Module. It declares a new instance of the first Form, frmUserDefined, as an Event Listener Object variable named ofrm.

    This is important because it enables the second Form Module to capture and respond to events that are raised by the first Form. By the above declaration, the Listener Object in the second Form can intercept and handle user-defined events raised by the frmUserDefined.

    Without this declaration, the second Form would be unable to capture and respond to events raised by the first Form, and the user-defined event functionality would not work as intended.

    To make a long story short, we are declaring an Instance of the frmUserDefined Form's  Class Module Object Variable name ofrm. The Form_  prefix to the Form Name indicates that we are referring to the Form's Class Module and will create an Instance of the Class Module.

    The first Form's Class Module Object Instance will be assigned to the declared ofrm Object Variable through the frmUDCapture Form's Form_Load() Event Procedure.

    The WithEvents keyword declares the ofrm as a Listener Object to Capture the Events Raised from its original Class Module-based user-defined Events.

    Note: Remember the WithEvents classification can be made only when an instance of any Object, like Form, TextBox, ComboBox, etc., is created.

    The general rule of Object Instance creation as a Listener Property is that:

    • The WithEvents keyword is used in the parent Class Module to create a connection between the object and its events. This connection allows the Class Module to capture events fired by the object and call the corresponding event procedure in response.

      The Event Subroutine name must start with the object instance name, followed by an underscore, and then the event name. This naming convention ensures that Access can properly map the event to the correct event procedure.

      For example, if we have a TextBox object Instance named "txtName" and we want to capture its LostFocus event, we would declare it in the parent Class Module like this:

      Private WithEvents txtName As Access.TextBox
      
      Private Sub txtName_LostFocus()
          ' Do something when the txtName TextBox loses focus
      End Sub
      

      In this example, the object instance name "txtName" is prefixed to the event name "LostFocus" to create the event procedure name "txtName_LostFocus".

    • To capture the user-defined event QtyUpdate() from the frmUserDefined form module, the parent form module instance should be declared with the WithEvents keyword. This allows the frmUDCapture form to listen to the event transmission from the frmUserDefined form and execute the corresponding event procedure.

    • Note: When you add a TextBox (or any other control) to a form in Access, the Access system creates an instance of the TextBox object and adds it as a control to the form. The Access system also creates a property for the TextBox in the form's module, which is declared with the WithEvents keyword. The Name property will be filled with a default name followed by the text "As TextBox" appended to it. For example, if the default name of the TextBox is "Text0", the property declaration of the TextBox in the form's module will be:

      Private WithEvents Text0 As TextBox

      You can change the name Text0 of the TextBox control to something more meaningful like Quantity, and the name of the property declaration in the form's module will automatically update to reflect the name change. This declaration is not directly visible to us except the Name Property Value. But can be displayed through the Object Browser.

    • Check the last two lines at the bottom of the Object Browser image below and see how the TextBox Object Instance name OrderQty declaration appears on our Form frmUserDefined. Check the declaration starting with the Keyword WithEvents OrderQty As TextBox.

      To inspect the Property of the TextBox in the Object Browser or any other control on the Form, you can follow these steps:

      1. Right-click on the VBA Editing Window and select Object Browser Option, or Click on the Object Browser Button from the Toolbar above to Open the Object Browser.

      2. In the Object Browser, select the Database Name in the <All Libraries> Control.

      3. The Object Browser will display the Properties and Methods of the selected control, including the declaration for the control's Object Instance.

      4. Select the Form Name Form_frmUserDefined from the left panel of the Browser Window.

      5. The Form's Property List appears in alphabetical order in the right-side Panel.

      6. Scroll down and find the OrderQty Property of the TextBox and select it.

      7. For example, the declaration for a TextBox with the name OrderQty on a form named frmUserDefined would look like this:

        WithEvents OrderQty As TextBox
        

        At the bottom panel of the Object Browser in the detail view of the selected Property.

      • You may look for the user-defined Event QtyUpdate() declaration and how it appears in the right panel with a lightning icon and check the details view of the event declaration in the bottom panel.

      • Both user-defined events and built-in events of Access objects require an event procedure to be created on their parent VBA module. For built-in events, the event procedure name will be based on the name of the Access object and the event name. For example, if the Access object is a Textbox named "Text0" and the Event is "LostFocus", the event procedure name will be "Sub Text0_LostFocus()".

        The User-defined Event Subroutine name consists of two parts. The first part is the Event Subroutine name Prefix, and the second part is the Event Name. The first part is the First Form Module's Instance name declared with the WithEvents keyword in the second Form Module - objFrm. The second part is the QtyUpdate, like objFrm_QtyUpdate(), both parts are separated with an underscore character. 

      • The LostFocus event is an inbuilt event of the TextBox control in Access. When you add a TextBox control to a form, Access creates an instance of the TextBox object and assigns it a default name such as Text0. This TextBox instance is then declared as a property of the form, with the WithEvents keyword, which allows it to capture and handle its inherent events.

        As I mentioned, the Text0 object's parent object is the class module of the form, which means that the event handling code in the LostFocus event (or any other inbuilt event) should be written in the form's class module. The naming convention for event procedures for controls is that they start with the name of the control instance, followed by an underscore, and then the name of the event. In the case of the TextBox control named Text0, the event procedure for the LostFocus event would be named Sub Text0_LostFocus().

        Several Textbox object instances can be created in the same Form with appropriate names, and their Event Subroutines are written in the same Form's Class Module with TextBox names as the prefix. This is what we do normally on Form Module.

      • The QtyUpdate() event is a user-defined event and is not part of any built-in object like the LostFocus event of a TextBox. It is defined in the Class Module of frmUserDefined Form and the parent object of the event is the form itself.

        In the frmUDCapture form, we have declared an instance of the frmUserDefined form as a listener parent object using the WithEvents keyword. Since the Form Class Module instance is named "ofrm", the event procedure for the QtyUpdate() event will have the format "Sub ofrm_QtyUpdate()". This allows the frmUDCapture form to capture and respond to the QtyUpdate() event raised by the frmUserDefined form.

      The statement Set ofrm = Forms("frmUserDefined") statement within the Form_Load() Event Procedure of the frmUDCaptured Form Module, and if the Form is in an open state, then assigns it to the ofrm Class Module object. If this form is not in the open state, then it will run into Error.  The On Error Resume Next statement will suppress the error message, but the Event capturing will not work as expected. 

      Ensure that you open the frmUserDefined Form first, then the second form frmUDCapture next.

    • We enter some Numeric Quantity Value into the OrderQty Textbox on the Form frmUserDefined and press the Enter Key to complete the data entry. The RaiseEvent QtyUpdate(Me!OrderQty) is fired from within the OrderQty_AfterUpdate() Event Procedure.

    • This Event is captured in the frmUDCapture Form's Event Procedure Subroutine Code given below: 

      Private Sub ofrm_QtyUpdate(sQty As Single)
      Dim Msg As String
      
        If Nz(sQty, 0) < 1 Or Nz(sQty, 0) > 5 Then
           Msg = "Valid Qty Range: 1 - 5 Only"
        Else
           Msg = "Order Qty: " & sQty & " Approved."
        End If
        
        Me.Label1.Caption = Msg
        
        MsgBox Msg, vbInformation, "QtyUpdate()"
      
      End Sub
      
    • The Valid value Range accepted in the TextBox is 1 to 5 only. Normally we write this validation check procedure in the same Form Module Event Subroutine only. Here, we are running the validity check in the second Form Module, and an appropriate message is written on the Msg String Variable and displayed in the MsgBox too.  It will update the same message text in the caption of Label Control on the second Form, to view the message after closing the Message Box.

    • Check the Event Subroutine line: Private Sub ofrm_QtyUpdate(sQty As Single)

      The frmUserDefined Form's Class Module Object Instance name ofrm is used as the Subroutine name prefix combined with the User-Defined Event name QtyUpdate(), both separated with an underscore character. 

    • Similarly, the FormClose() Event is captured and runs the Private Sub ofrm_formClose(txt As String) Event Procedure and it displays a message to the User, whether to close the Form frmUserDefined (first form) now, or not. If the response is Yes then it closes the First Form.

      Private Sub ofrm_formClose(txt As String)
      Me.Label2.Caption = txt
      
      If MsgBox(txt, vbYesNo, "FormClose()") = vbYes Then
        DoCmd.Close acForm, ofrm.Name
      Else
        Me.Label2.Caption = "Close Action = Cancelled."
      End If
      
      End Sub 
    • In the formClose() Event Procedure header line you can see that the ofrm object prefix is appearing. In this subroutine, the parameter text is shown in the message box and the user responds whether the frmUserDefined Form (the first Form) is to be closed now or not.  The Message text is displayed from the second Form's Event Procedure. If the response is Yes then it runs the Code to close the first Form.

      When we issue Docmd.OpenForm  "FormName" Command, a series of Events takes place in a certain order, Form Open, Form Load, Form Current in that sequence. Similarly, when we run the DoCmd.Close acForm, "FormName"the Form_Unload Event runs first before the Form_Close event, if both events are run in VBA.  

    • So when we run the above Form_Close Event Procedure to close the first Form, the first Form prepares to close down. Since we have written the Form_Unload Event Procedure in the first Form, to Close the second Form, it runs first and closes the second Form, before closing the first Form.

The User-Defined Events Trajectory View.

Events Trajectory View.

From the above graphical image, we can easily see at a glance how the user-defined Event is declared with the Event Keyword, how the Events are Announced with the RaiseEvent Keyword, and how the Event Listener is declared with the Keyword WithEvents that Captures the announced Event, identify it with the Event Procedure, and executes the correct Event Procedure Code. 

If we learn the inner workings of the Events and Event Procedure we can write Event Procedures of one Form in another Form's Class module, or Event Procedures can be in stand-alone Class Modules, rather than writing all the Event Procedures on the same Form Module. We are really heading for that.  But before touching the tricks at that level, we need to know a few basics of Event handling on Forms. How we could do things differently on Form, other than the traditional method we follow now.

Once we know how these tricks work together, our target is to take all the VBA codes out of the Form Module and organize them in such a way that Coding becomes very easy.  We will use the Forms for User Interface design only. 

Besides that, once we streamline and adopt the new Coding procedure, we can easily take the backbone of the new procedure to other Projects and coding will become easier, enabling faster completion of new Projects.

Download Demo Database

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
  19. Streamlining Event Procedures RGB Color Wizard-19
  20. Streamlining Event Procedures Number To Words-20
Share:

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. 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
  19. Streamlining Event Procedures RGB Color Wizard-19
  20. Streamlining Event Procedures Number To Words-20

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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