Create Reusable, Structured Form Module VBA Code with a Standalone Class Module.
Eliminate repetitive, unorganized form module code and accelerate Microsoft Access application development.
Note: Each article includes a downloadable demo database for self-paced learning and hands-on exploration of the concepts explained.
Reusing Form Module VBA Code for New Projects.
Defining Custom Events in Microsoft Access - Part Two
Objects and Their Built-in Events - Part 3.
Standalone Class Module and Events - Part Four
Several TextBoxes and Event Capturing - Part Five
Class Objects and Wrapper Classes - Part Six
Form Module vs. Reusable Class Module Coding Demo - Part Seven
The Normal Coding method.
- The Stand-alone Class Module supported method.
Two Stand-alone Class Modules supported the option.
Collection Object replaces Class Object Array - Part Eight
Reusability of Streamlined VBA Code - Part Nine
Organizing Wrapper Classes for Different Forms - Part Ten
ComboBox and Option-Group Wrapper Classes - Part Eleven
Report Module Code in Class Module - Part Twelve
Hiding Report Lines Conditionally - Part 13.
Form Report Detail Sections Event Handling - Part 14.
- New Custom-Made Form Wizard VBA - Part 15.
- New Custom-Made Report Wizard - Part 16.
- Streamlining VBA External Files List in Hyperlinks-17
- Streamlining Event Procedures 3D-Text Wizard-18
- Streamlining Form Module VBA RGBColor Wizard-19
- Form VBA Structured Coding Numbers to Words Converter-20
- Form VBA Structured Coding Access Users-Group Europe Presentation-21
- The Event Firing Mechanism in Access Objects-22
- One TextBox and Three Wrapper Class Instances-23
- Streamlining Code Synchronized Floating Popup Form-24
- Streamlining Code Compacting/Repair Database-25
- Streamlining Code Remainder Popup Form-26
- Streamlining Code Editing Data in Zoom-in Control-27
- Streamlining Code Filter By Character and Sort-28
- Table Query Records in Collection Object-29
- Class for All Data Entry Editing Forms-30
- Wrapper Class Module Creation Wizard-31
- wrapper-class-template-wizard-v2
The Existing Form Module Coding Approach.
Access forms include a wide range of controls—such as text boxes, command buttons, and combo boxes—each designed for specific tasks. Typically, we write event procedures within the form’s class module to handle these tasks.
However, challenges often arise when a single control, such as a text box, requires multiple event subroutines. The related code becomes scattered across different event procedures, intermingled with code for other controls within the same form module.
This lack of structure can make the development process cumbersome. Developers frequently switch between form design view (to adjust the user interface or event properties) and the form’s class module (to locate or refine event code). Over time, this repeated navigation slows down workflow and makes maintaining event code more difficult. Continue...
When designing a Microsoft Access form, we typically add controls such as Text Boxes, Command Buttons, and Combo Boxes. Each control provides a set of built-in events that are triggered in response to specific user actions or system-generated operations. These events are handled by their associated event procedures, where we write VBA code to perform the required tasks.
Traditionally, we have built forms by adding controls, relying on their built-in events, and writing VBA code to respond to them—often without considering how the underlying event mechanism works. Continue...
The primary objective is to move VBA code from the Form Module to a standalone Class Module, reserving the Form exclusively for user interface design. Is this feasible? Absolutely. The following pages will demonstrate how this can be achieved. Once this coding approach is implemented, significant portions of the code can be reused across new projects, simplifying development and reducing coding effort.
With this coding methodology, Code Modules can be opened and edited independently, eliminating the need to repeatedly switch to Form Design View simply to access the code. This saves considerable manual effort and improves development efficiency, resulting in faster project completion. The proposed solution will continue to work seamlessly with the controls on the Form, preserving the standard behavior from the end user's perspective. In addition, the underlying framework of this approach can be exported and incorporated into other projects, making code reuse and maintenance significantly easier. Continue ...
The Quantity TextBox object's events are enabled by selecting the [Event Procedure] option in the control's Event Property settings. When an event is raised (fired) by the TextBox object instance, it is captured by the Form's Class Module, where the corresponding event procedure is executed. This process is illustrated in the graphical image below.
The events raised (fired) by the Quantity TextBox on the Form can also be captured in the standalone `Class1` Class Module. To achieve this, an instance of the TextBox object must be declared in `Class1` and qualified with the `WithEvents` keyword. A reference to the Quantity TextBox control on the Form is then assigned to this object variable in the `Class1` Class Module. Continue ...
Next, let us examine how to handle events for multiple TextBox controls on a Form. For this exercise, we will place three TextBox controls on the Form and use them to explore event handling through a standalone Class Module.
In practical applications, a Form may contain 10, 20, or even more TextBox controls. While it is unlikely that every control will require event procedures, this approach can be extended to handle as many controls as necessary when the application design demands it. Continue ...
After experimenting with the previous examples, you should now have a clear understanding of how the Events Subroutines of TextBox objects work on a Form and how they can be relocated into a standalone Class Module to run the Event Procedures for specific tasks on the Form.
In our earlier experiments, we worked mostly with a single TextBox to understand the fundamentals. The only exception was in Part Five, where we extended the idea to three TextBoxes to demonstrate how to enable events and capture the fired events within a standalone Class Module. It is important to note that all of these actions were carried out entirely in the standalone Class Module, rather than in the Form module. Continue ...
Some readers may still be in doubt about the effectiveness of these new Event-Procedure coding methods. This page demonstrates the traditional Form Module coding alongside the streamlined standalone Class Module Coding advantage. The demo is divided into three parts, ranging from standard coding to advanced automation, which helps determine which approach works best.
On January 3, 2024, I presented a concise overview of the intricate topic: Streamlining Form Module Code in Standalone Class Module for the Access User Groups (Europe) Chapter Live, moderated by Mr. Colin Riddington & Mr. Alessandro Grimaldi, with the Demo Database attached to this Article.
You can view the Live Presentation supported by PowerPoint Slides in a YouTube Video by clicking the above Link.
The YouTube subtitles are available in eleven languages. Continue...
In the past, we used the TextBox Wrapper Class Array to store TextBox instances with Events enabled, allowing us to capture them in memory and execute their associated Event Subroutines. While this Array-based method worked, it required frequent re-dimensioning and the maintenance of separate indices for different object types, such as Command Buttons, ComboBoxes, and List Boxes. Fortunately, there is a more efficient solution—the Collection Object. Continue ...
The OnDirty Event Subroutine.
Private Sub txt_Dirty(Cancel As Integer)
If MsgBox("Editing the " & UCase(Txt.Name) _
& ": Value? " & Txt.Value, vbYesNo + vbQuestion, _
Txt.Name & " DIRTY()") = vbNo Then
Cancel = True
End If
End Sub
The code snippet above monitors all TextBoxes on the Main Form Employees and Orders subform to prevent unintended changes. When the user attempts to modify any data field, a warning message appears, requiring confirmation before the edit can proceed. If the user recognizes the action as a mistake, he/she can cancel the event and restore the field to its original value.
This approach helps prevent accidental modifications and maintains data integrity.
If you have 15 TextBoxes in the Form, the traditional coding needs to repeat the above 5 lines of code in fifteen different Subroutines in the Form's Class Module, instead of one time in the Standalone Class Module. Continue...
Wrapper Class Templates
Create a standard set of wrapper class templates for all frequently used controls, such as TextBoxes, CommandButtons, ComboBoxes, and others. When implementing these templates for a specific form, create copies of the required classes and rename them using a short, meaningful prefix that identifies the form. For example, for the Employees form, the prefix `Emp` can be used, resulting in class names such as `EmpTextBox`, `EmpCmdButton`, and `EmpComboBox`.
This naming convention clearly associates each wrapper class with its corresponding form, making the code easier to identify, maintain, and manage while promoting consistency throughout the project.
After reviewing the previous nine articles on streamlining form module VBA code, you should now be familiar with this new coding approach and understand how to create a new wrapper class whenever a required template is not already available—such as when a new control Type is added to the form. Continue ...
Wrapper Class Design.
In this episode of the Streamlining Form Module VBA Code series, we will create Wrapper classes for ComboBox and OptionGroup controls. Having worked through the previous episodes, you are now familiar with the process of creating Wrapper Class Modules for other controls, including TextBox, ListBox, CommandButton, and TabControl. You have also learned how to implement event procedures within these Wrapper Class Modules instead of placing them directly in the Form Module.
ComboBox and OptionGroup Control.
In this session, we will focus on the usage of ComboBox and OptionGroup controls, as illustrated in the form image below.
The Order Detail data displayed in the ListBox is filtered through the query OrderDetailQ1, which is based on the Employee ID selected in the ComboBox control named cboEmp, located above the ListBox. The SQL statement for this query is shown below: Continue ...
Report Module VBA Code in Standalone Class Module.
In the earlier episodes of this series, Streamlining Form Module Code in the Standalone Class Module, we explored how to handle Event Subroutines for frequently used Access controls within a standalone Class Module. This approach allows for managing event handling in Standalone Class Module(s), without interfering with the Form design task, resulting in cleaner, more maintainable code.
If you’re new to handling Form/Report Module Event Procedures in standalone Class Modules, I suggest starting with the earlier episodes in this series. These articles provide the necessary background and step-by-step explanations to help you understand and apply this approach effectively. Links to all previous articles in the series are provided at the end of this page. Continue ...
To visually emphasize specific Report line records that do not meet the required marks criteria, a red ellipse is drawn around the corresponding TextBox. Traditionally, this procedure is implemented directly in the Detail_Print Event Subroutine within the Report’s Class Module. The Report Detail Format, Report_Page Events.
The Report Detail Section Format Event runs before the Print Event. During this phase, the Report Detail Format Event arranges the data records line by line within the Detail section.
Other events, such as the Report Header, Report Footer, Page Header, Page Footer, and Report Page Events, also occur during the formatting phase. When these Section events are enabled, code can be written within them to control or manipulate the layout and the report's preview or print. Continue ...
All other Frequently Used Controls on a Form.
We have learned how to capture events fired by Form or Report controls and handle them through Event Subroutines in their respective Wrapper Classes. The TextBox and other objects' Wrapper Classes are enabled with the actual references of the controls on the Form, allowing us to interact with them seamlessly. Beyond event handling, any other control on the Form can also be accessed from any Wrapper Class to read or update its values. This flexibility opens the door to creative features such as running animations, simulating a digital clock on the Form, implementing a countdown timer before the Form is closed, and much more.
For such activities, every Wrapper Class includes a Form object instance, to which the active Form’s reference is assigned. This allows the Wrapper Classes to interact directly with the Form. When you need to handle events such as the Form_Timer() Event or the MouseMove Event on the Form’s Detail Section, the Form instance must be declared with the WithEvents keyword (Private WithEvents frm as Form) inside the Wrapper Class. Since the Form instance is always included in a Wrapper Class, any of the above-mentioned features can be coded within the Intermediary Class Module or in any control’s Wrapper Class. However, if the Form-level events are not used, the form object declaration can be like: Private frm as Form.
The Form Detail Section declaration,
Private frm as Access.Form Private WithEvents SecDetail As Access.Section Set SecDetail = frm.Section(acDetail) SecDetail.OnMouseMove = "[Event Procedure]"
Report Detail Section Declaration.
Private WithEvents Rpt as Access.Report Private WithEvents RptSec as Access.[_SectionInReport] Set RptSec = Rpt.Section(acDetail)
Continue ...
No comments:
Post a Comment
Comments subject to moderation before publishing.