Streamlining Form Module Code Part 14 ~ LEARN MS-ACCESS TIPS AND TRICKS

All 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 control Wrapper Class Instances created are assigned with the References of the actual 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 the 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 closing the Form, 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 Class 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 inside the Wrapper Class. Since the Form instance is always included, any of the above-mentioned features can be triggered from within any control’s Wrapper Class. However, if capturing Form-level events is not required, the instance can be declared without WithEvents.

The Form Detail Section declaration is as given below:

Private frm as Access.Form
Private WithEvents SecDetail As Access.Section

Set SecDetail = frm.Section(acDetail)
	SecDetail.OnMouseMove = "[Event Procedure]"

Report Detail Section Declaration and Reference assignments are like the following:

Private WithEvents Rpt as Access.Report
Private WithEvents RptSec as Access.[_SectionInReport]

Set RptSec = Rpt.Section(acDetail)

By moving Event Subroutine VBA Code out of the Form and Report modules and placing it in standalone Class Modules, we can work with the code independently while keeping the design surface clean. The Collection object plays a key role here, as it allows multiple instances of the same control type (such as TextBox controls on a Form) to remain in memory, each enabled with the required Events. This approach keeps the Form design process separate from the coding task, making both development and maintenance more efficient.

Episode Seven is a classic example that demonstrates the strength of this new approach—shifting Form/Report module code into standalone Class Modules. With this method, repetitive manual coding is greatly reduced. For instance, a single pair of GotFocus() and LostFocus() routines can manage any number of TextBoxes on a Form, without writing separate code for each one. Likewise, if ten TextBoxes require AfterUpdate() Event Subroutines, each with different validation rules, all of them can be handled within a single AfterUpdate() procedure in the standalone Class Module. This principle is not limited to TextBoxes—it applies uniformly to all types of controls on the Form.

Event Procedure code becomes better organized when placed in Wrapper Classes, making it easier to maintain and debug—without interfering with the Form’s design.

Private Sub Txt_AfterUpdate()
  Select Case Txt.Name
  	 Case "Quantity"
     	'Code
     Case "UnitPrice"
     	'Code
     Case "TaxRate"
     	'Code
     Case . . .
     
  End Select
End Sub

If any TextBox’s AfterUpdate event code needs modification, or if a new TextBox requires an AfterUpdate event subroutine, there is no need to open the Form in Design View, locate the TextBox control, open the Property Sheet, find the event property, and then click the Build… button to access the event procedure. Instead, we can simply open the Class Module directly and make the necessary changes. Compare this with the traditional approach, where developers often waste considerable time repeating the same steps for each control event, sometimes multiple times for the same control.

I am confident you will recognize the difference once you adopt this new coding approach. While it may take a little time to become familiar with the concept—especially if you are new to VBA development—the benefits in efficiency and maintainability are well worth the effort.

The Form shown below incorporates several controls that were designed in earlier episodes, with their corresponding Wrapper Class Modules imported to handle the control event procedures. In addition, animations such as scrolling text, a digital clock, and a countdown timer (displayed before the form closes) have been implemented within the Class Module to run seamlessly on the Form.

Everything you can do in a Form Module can also be achieved from a standalone Class Module. The real advantage comes when you shift your mindset to this approach—working differently, more effortlessly, and in a well-organized manner, with the added benefit of reusing your VBA code across projects, often with little or no modification. Once you experience this, the difference becomes clear.

Anything attempted for the first time feels difficult—that’s how most of us began our journey with VBA coding, spending countless minutes or even hours solving problems through trial and error. I retired from service 13 years ago, yet I continue to learn new things every day and share my experiences with others. Learning never truly stops; in fact, it becomes even more meaningful when we pass it on to others.

Technology is advancing at an incredible pace, and it is inspiring to see children as young as 10 to 15 years old already practicing coding and building apps. What once took us years to discover, they now explore at such an early age—truly fortunate learners of this generation!

I was first introduced to computer-related automation at the age of 27, during my service in the Indian Navy Defence Establishment. My initial exposure came through the Key Punch Machine, where I worked on panel wiring to automate tasks such as skipping or duplicating specific columns of an 80-column punch card—the standard input medium for programs and data at that time.

The mainframe system we used was an ICL 1901, equipped with disk drives, and the programming languages of choice were COBOL and FORTRAN. I even managed to learn a bit of COBOL back then, in 1975, which became my earliest step into the world of programming.

If you need some Class Module beginner lessons, they are available starting with this Link: MS-Access Class Modules and VBA series of a few Articles.

The Demo Database With All Frequently Used Controls.

The attached sample Demo Database contains the following Form; the download link is available at the end of this page, and the main Form (frmControls_All) Image is given below.

Right-click to open Large Image in New Tab/Window.

On the top-left side, the form features a TabControl-based menu designed with three layers of options, each hosted on separate Tab Pages. To achieve a clean interface, the Tab Page style is hidden by setting its property to None. Navigation between these menu pages is handled through dedicated command button clicks. Each button triggers a TabPage Change event, which in turn displays a message showing the corresponding Page Index number.

The three available menus are Tables, Forms, and Reports. The TabControl, along with its associated menu selection command buttons, was copied from the earlier episode’s form and pasted into this one. Some menu options were modified to replace them with other Forms and Reports introduced in previous episodes. The corresponding Class Modules were also imported into this database to ensure proper execution of these features.

Imported Class Modules of TabControl and the Menu changing Command Buttons.

The following Class Modules were imported into this Database, from an earlier episode, to run the Menu options with a few changes:

TabLst_Object_Init
TabLst_CmdButton
TabLst_ListBox
TabLst_TabCtl

The Tab Page with the name TABLES has three options: Employees, Orders, and Customer Tables. Double-click on an Item to open it. Before opening the Table, a Female Voice will announce the Table Name before it appears on the Screen.

Employees and Orders Form.

On the FORMS TabPage, the first option is the Employees Form with an embedded Orders SubForm. In the footer section of the Employees Form, you will find a Search and Find TextBox that allows you to look up records using the EmployeeID as the search key. When a search is performed, a label will briefly flash for a few seconds to indicate whether the search was successful or not. For testing, try entering an EmployeeID greater than 9—this will demonstrate the “not found” result.

Right-click to open Large Image in New Tab/Window.

Note: You may try to implement the Female Voice to announce the success or failure of the search operation.

The above Form and its VBA Codes are running from the following Class Modules, with the Emp Prefixes:

EmpObject_Init
EmpCmdButton
EmpTextBox
EmpCombo

The Employee-wise Orders Freight Sales Analysis.

The second option on the Forms Menu is the Employee-wise Orders Freight-Sales Analysis Form (frm_OptionGroup2), which includes a Graph Chart for visual analysis. This form features an Option Group Control with three selectable options:

Highest Freight Sales Value
Lowest Freight Sales Value
Total Freight Sales Value

Based on the selected option, the corresponding result is displayed in a TextBox, accompanied by an animated label caption—a scrolling text that moves from right to left for added visual effect.

Right-click to open Large Image in New Tab/Window.

The above form is powered by two standalone Class Modules, as shown below. Both the Form and the Class Modules have been imported from one of the earlier episodes.

Opt_Object_Init
Opt_Frame2

The Option Group Subroutine VBA code is executed from the Opt_Frame2 Class Module. The Employee ComboBox, the Freight Value display TextBox, and the Command Button Click Event Subroutines are handled through the Opt_Object_Init Class Module. Since each of these controls has only a single instance on the form, separate Wrapper Classes were not created for them. The Graph Chart on the Form.

The Graph Chart’s source data comes from the OrderSummaryQ2 query, which in turn is based on the OrderDetailQ2 query. The OrderDetailQ2 query filters data from the Orders table using the EmployeeID selected in the cboEmp ComboBox. A hidden TextBox named EID contains the expression =[cboEmp], which copies the EmployeeID selected in cboEmp. This value is then used in the Link Master Field property of the Graph Chart to dynamically update the chart whenever a different EmployeeID is chosen from the ComboBox.

The third TabControl page contains three report options:

Employee Records
Students Exam – Failed Cases (highlighted)
Students Passed/Failed Listing (from the same report)

The last two reports, along with their corresponding Class Modules, have been imported into this database from earlier episodes. The following stand-alone Class Modules handle the Event Subroutine VBA code for these reports:

ClsStudentHighlight0
ClsStudentsList

The ClsStudentHighlight0 Class Module runs the Detail_Print Event Subroutine Code for the Report StudentsHighlight_Class0.

The Report Image is given below:

Right-click to open Large Image in New Tab/Window.

The ClsStudentsList Class Module runs the Detail_Format() and Report_Page() event subroutines. Additionally, the PageBorder() function is called from the Standard Module to draw two border lines on each page of the report. An ellipse is also drawn around the TextBox in the Detail_Print() event of the report.

The Students Exam Passed List Image is given below.

Right-click to open Large Image in New Tab/Window.

The Exam Failed Cases Listing is generated from the same report. Both listings are created by hiding the Report Detail section for records that do not meet the specified criteria, controlled through the Detail_Format() event.

Right-click to open Large Image in New Tab/Window.

The SubForm on the Main Form.

Next, a SubForm contains three TextBoxes. The Quantity TextBox accepts values only in the range of 1 to 10, while the Unit Price TextBox must contain a non-zero value. These entries are validated in the OnExit() event subroutine of the ClsText Class Module before being accepted. The TotalPrice TextBox uses its OnGotFocus() event subroutine to calculate the total price (Quantity * UnitPrice) and populate the result into the TextBox. This calculated value is also displayed in the Label control positioned above the SubForm.

The SubForm is introduced here to demonstrate how to scan its controls and enable the required events within the Class_Init() subroutine of the ClsControls_All Class Module. This process is handled through two For...Next loops. The first loop begins with the statement:

For Each ctl In fom.mySubForm.Form.Controls

For the SubForm scanning, and the other starts with:

For Each ctl In fom.Controls

to scan for the required controls and enable their Event Procedures.

Note: If you place any TextBox on the TabControl pages, they are still treated as part of the main Form. Ensure that their names do not conflict with other TextBox names already present on the Form.

The Form contains a ComboBox with a list of country names. When a country is selected, its list index number is used to automatically select the corresponding capital city in the ListBox. Additionally, clicking an item in the ListBox will display the selected value in a message box, confirming that the Click event is being captured in the Class Module.

The Option Group Control.

The Option Group control is not linked to any specific function. However, when a radio button is clicked, it displays the index number of the selected option. This serves as confirmation that the event has been fired and successfully captured in the Option Group Class Module ClsOption.

The Employee Orders command button opens the form containing the Graph Chart (included in the TabControl menu) directly. The next button, labeled Heading Color, changes the ForeColor property of the form’s heading label.

The Close button triggers a 10-second countdown on the label positioned above the SubForm. Once the countdown reaches zero, the form frmControls_All automatically closes.

The Class Modules associated with the main form controls are named with the prefix Cls….

You can view the complete list of these Class Modules in the declaration section of the ClsControls_All Class Module.

Private tx As ClsText
Private cmd As ClsCmdButton
Private cbo As Clscombo
Private Lst As ClsListBox
Private Opt As ClsOption

Demo Database Download Link:

MixAllControls.zip

Streamlining Form Module Code in Standalone Class Module.

borgeJanuary 27, 2024 at 7:04 AM
Your link to MixAllControls.zip does not work! It renders DirListing2K1.zip instead. Can you please fix?
a.p.r. pillaiJanuary 27, 2024 at 7:35 AM
Sorry about that. Thank you for pointing out the Error. Now, the error is fixed, please try it again.