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

All Frequently Used Controls on a Form.

We learned how to capture the Event fired from the Form or Report Controls and write the Event Subroutine VBA Code in the Control Wrapper Classes. The control Instances created in the Wrapper Class are assigned with the References of the Controls on the Form. Besides that, any other control on the Form is accessible from the Wrapper Class, to save or retrieve their values. Run Animations from Wrapper Class on the Form, using other controls or their content, mimic a digital clock on the Form, run a countdown before closing the Form and so on.

For this kind of activities, we always include a Form Object Instance in all the Wrapper Classes and assign the active Form reference to the Form object Instance. For running a Form_Timer() Event or any other Form Event Procedure like MouseMove on the Detail Section of the Form the Form Instance declaration must be qualified with the keyword WithEvents in the Wrapper Class. Any of the above mentioned activities can be run from any Control's Wrapper Class, because the Form object Instance is included. You can declare it with or without the WithEvents keyword. But if you need to capture the Form Event then declare it with the keyword 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)

We could keep the Event Subroutine VBA Code away from the Form and Report Modules and work with the VBA Code independently in the standalone Class Module. The Collection object plays a major role in keeping several instances of the same type of object in memory after enabling them with required Events, like the TextBox on the Form, without interfering with the Form Design task.

The earlier Episode Number Seven is a classic example that shows the power of this new approach of Form/Report Module Coding in the Standalone Class Module, which automates a major part of the manual Coding work with a single GotFocus() and LostFocus() pair for any number of TextBoxes added on the Form. Similarly, if there are ten TextBoxes on the Form that need the AfterUpdate() Event Subroutines with different Validation requirements for each of them, then all of them can be written in a single AfterUpdate() Event Subroutine in the stand-alone Class Module. This rule is applicable to all Types of Controls on the Form as well.

The Event Procedure Codes are better organised in Wrapper Classes, easily reachable for maintenance, debugging without interfering with the Form 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 of the TextBox's AfterUpdate Events need any change to the Code or any other new TextBox needs to implement the AfterUpdate Event Subroutine then no need to open the Form in Design View, select the TextBox Control, display the Property Sheet, look for the Event Property and click on the build . . . Button to open the specific Event Subroutine to write/modify the Code. All we need to do is to open the Class Module directly and make changes. Imagine how much time is wasted in the traditional way of Coding on each Control Type in the Form, attending more than once for the same control Event Subroutine in this way.

I am sure you will know the difference once you set your mind to pursue the new way of Coding technique, even though it takes a bit of time to get oriented with the new concept if you are a beginner in VBA Coding.

The Form Image given below is designed with some of the Controls designed in the earlier Episodes and the Wrapper Class Modules are Imported for running in this Form's Control Event Procedures. Animations of Text, a Digital Clock, and a Count-down display before closing the Form are also included in the Class Module to run on the Form.

You can do anything from the stand-alone Class Module as you do normally in the Form Module. Once you set your mind to do things differently, effortlessly, and better organized with an outlook to reuse the VBA Code with or without minor changes then you will know the difference.

Anything for the first time is difficult, that's how we all started learning VBA Coding, spending many minutes or hours on trial and error basis for solving a problem. I retired from service 13 years ago and still trying to learn new things and share with others.

New technology emerges every day and even kids aged 10 to 15 years of age (lucky kids) started to do Coding practices and App developments!

I was exposed to some form of Computer-related automation only at the age of 27. The Key Punch Machine's Panel Wiring task for automatically skipping or duplicating some columns of 80 column Punch Card, the input medium of Programs and Data at that time, in the Indian Navy Defence Establishment. The Main-Frame Computer was an ICL 1901 Machine with Disk Drives, the Programming Languages were COBOL and FORTRAN, and I learned a bit of COBOL Language at that time (1975) too.

If you need some Class Module beginner lessons they are available starting with this Link: MS-Access Class Modules and VBA series of 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.

The top left side TabControl-based Menu has three layers of options on three Tab Pages. The Tab Page Style is hidden by selecting its Property setting to None. The Menu Pages are selected with separate Command Button Clicks. The TabPagee change Event will be fired on the Command Button Clicks and will display a message with the Page Index Number.

The three Menus are Tables, Forms, and Reports. The TabControl along with the Menu selection Command Buttons were Copied from the earlier episode Form and Pasted on this Form and made some changes to the Menu Options to replace with some other Forms and Reports presented in the earlier episodes. The related Class Modules are Imported to run them in this Database.

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 Orders SubForm. It has a Search and Find TextBox in the Footer Section of the Employees Form. The EmployeeID number is the Key for Search operations. If the Search was successful/or failed then a Label will flash a few seconds announcing the search operations result. Try the EmployeeID number above 9 to test the Failed result.

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) with a Graph Chart. The Form has an Option Group Control with three Options; 1. Highest Freight Sales Value, 2. Lowest Freight Sales Value, and 3. Total Freight Sales Value. The result is displayed in a TextBox with an Animated Label Caption; moving Text from Right-to-Left.

The above form is driven by the two standalone Class Modules given below. Both the Form and Class Modules are imported from one of the earlier Episodes:

Opt_Object_Init
Opt_Frame2

The Option Group Subroutine VBA Code runs from the Opt_Frame2 Class Module. The Employee ComboBox, the Freight Value display TextBox, and the Command Button Click Event Subroutines are run from the Opt_Object_Init Class Module only. Separate Wrapper Classes are not created for them because the Control has only one Instance on the Form.

The Graph Chart on the Form.

The Graph Chart's Source Data is from OrderSummaryQ2 Query. The Source Data of this Query is taken from the OrderDetailQ2 Query. The OrderDetailQ2 data is filtered from the Orders Table using the EmployeeID selection in the ComboBox with the name cboEmp. There is a hidden TextBox on the Form with the name EID containing the expression: =[cboEmp] to copy the EmployeeID selected in the cboEmp ComboBox. This Value is used in the Link Master Field Property of the Graph Chart to reflect the change of Chart Value on EmployeeID selection in the cboEmp ComboBox.

The TabControl Page number three has three Report Options; 1. Employee Records, 2. Students Exam Failed cases highlighting, 3. Students Passed/Failed Listing from the same Report.

The last two Reports and their Class Modules are Imported into this Database from earlier Episodes. The following Stand-alone Class Modules run the Event Subroutine VBA Code:

ClsStudentHighlight0
ClsStudentsList

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

The Report Image is given below:

The ClsStudentsList Class Module-based Detail_Format() and Report_Page() Event Subroutines are run and the PageBorder() Function is called from the Standard Module to draw two Report Page Border lines on each page of the Report. The ellipse is drawn around the TextBox in the Detail_Print() Event of the Report.

The Students Exam Passed List Image is given below.

The Exam Failed Cases Listing is taken from the same Report. Both these Report listings are prepared, by hiding the Report Detail Section for those records that don't meet the Criteria, in the Detail_Format() Event.

The SubForm on the Main Form.

Next, there is a SubForm with three TextBoxes. The Quantity TextBox accepts a value range of 1 to 10 only. The Unit Price Value should be non-zero. The entered values in these TextBoxes are validated in the OnExit() Event Subroutine in the ClsText Class Module before accepting them into the TextBoxes. The OnGotFocus() Event Subroutine of the TotalPrice TextBox calculates the Total Price value (Quantity * UnitPrice) and inserts it into the TotalPrice TextBox. The result value is displayed in the Label Control above the SubForm too.

The SubForm is introduced here to demonstrate how to scan for the SubForm Controls and enable their required Events in the Class_Init() Subroutine (in the ClsControls_All Class Module). There are two For . . . Next Loop, one starts 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 will be treated as controls on the Form only and see that their names do not conflict with the other TextBox Names on the Form.

The ComboBox on the Form has a list of Country names. When a Country Name is selected in the ComboBox its list index number is used for selecting the Capital of that Country from the ListBox automatically. If you Click on an item in the ListBox it will display the selected value in a MsgBox to indicate that the Click Event is captured in the Class Module.

The Option Group Control.

Next, the Option Group Control is not connected with any specific function except when one of the Radio Buttons is clicked it will display the index number of the option as an indication that the selected Event is fired and captured in the Option Group Class Module ClsOption.

The Command Button (with the Caption Employee Orders) opens the Form with the Graph Chart (included in the TabControl Menu) directly. The next Command Button with the caption 'Heading Color' changes the Form Heading top Label Control's ForeColor.

The Close Command Button Click runs a 10 Seconds Count-Down on the Label Control above the SubForm. When it is zero the Form frmControls_All will close.

The Main Form-based control's Class Module names start with the prefix Cls...

You can see the List of these Class Modules in the declaration area of the ClsControlls_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.