Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents and Report Line Highlighting

Introduction

This is really a re-run of an earlier Post: Highlighting Reports published during August, 2007.  The full Code was written on the Report Class Module itself.  The change here is that we will free up the Report Class Module and transfer all the Code into the Class Module.  The Report Detail Section Print Event is captured in the Class Module Object and runs the Code to highlight the required Report line item.

If you have tried out the sample Reports in the Demo Databases in earlier two Posts then the Code we use in this Class Module (major part) is familiar to you. 

If you have not come across those Articles then their links are given below:


The Highlight of this Project

In the Report Detail Section OnPrint() Event Procedure draws an oval shaped circle, within the boundaries of the Text Box.  The Text Box holds the exam marks from Student's Table.  If the student is not successful in obtaining his/her pass marks of 60% or more then the Marks of such cases are highlighted with an oval shaped circle around the marks.

Sample Image of the Report Demo Run Print Preview is given below:

Even though the Report design is a simple one I would like to draw your attention to the Text Box where we draw the oval shape around the Marks.  

The oval shape is drawn within the boundaries of the Total Text Box.  The size of the Text Box should not be too wide, like the Remarks Label, or too short.  In either case there will not be any problem in attempting to draw the shape inside the boundaries of the text box, but some portion of the shape may overlap or may not  appear correctly around the marks within the Text Box.

The Class Module

More details on this when we go through the Class Module Code.

The Class Module: ClsStudentHighlight Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]
Private WithEvents secFutr As Access.[_SectionInReport]

Private txt As Access.TextBox
Private max As Access.TextBox
Private pct As Access.TextBox
Private lgnd As Access.Label

Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
Const strEvent = "[Event Procedure]"

On Error GoTo mRpt_Err

  Set Rpt = RptNewVal
  
  With Rpt
     Set secRpt = .Section(acDetail)
     Set secFutr = .Section(acFooter)
     secRpt.OnPrint = strEvent
     secFutr.OnPrint = strEvent
  End With

  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("Maxmarks")
  Set pct = Rpt.Controls("Percentage")
  Set lgnd = Rpt.Controls("Legend")

mRpt_Exit:
Exit Property

mRpt_Err:
MsgBox Err.Description, , "mRpt()"
Resume mRpt_Exit

End Property

Private Sub secRpt_Print(cancel As Integer, PrintCount As Integer)
'  Draw ellipse around controls that meet specified criteria.

Dim m_max As Double
Dim m_pct As Double
Dim curval As Double
Dim pf As Double
Dim pp As Double
Dim yn As Boolean

On Error GoTo secRpt_Print_Err

m_max = max.Value 'read Maxmarks TextBox Value
pp = pct.Value 'read Pass Percentage TextBox value

curval = Nz(txt.Value, 0) 'read obtained marks from Total TextBox
pf = Int(curval / m_max * 100 ^ 2) / 100 'calculate obtained marks percentage
yn = (pf >= pp) 'Passed or Not (TRUE/FALSE)

'call the DrawCircle Subroutine with Pass/Fail flag
'and the Control as parameters
Call DrawCircle(yn, txt)

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print"
Resume secRpt_Print_Exit

End Sub

Private Sub secFutr_Print(cancel As Integer, PrintCount As Integer)
Dim y As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

y = False 'set the flag false to draw oval shape
Set lbl = lgnd 'pass label control in Page Footer
Call DrawCircle(y, lbl) 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'if pass no highlighting, change logic for pass cases
    bolPrintCircle = False
Else 'highlight failed cases
    bolPrintCircle = True
End If

Set ctl = ovlCtl
        
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
           ' change this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
            ' Determine coordinates of ctl and to draw ellipse.
            ' Determine height and width of ellipse.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
            'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

            'calculate horizontal X coordinate of circile
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
            'draw an ellipse around the Total TextBox
            Rpt.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
          bolPrintCircle = False
        End If
    End If


DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit

End Sub

In the Class Module Properties the first line declares the Report Object in Rpt Variable.

Next two lines declares the Report Detail and Footer  Sections in secRpt and secFutr Object respectively.

Next three lines declares Text Box Objects for selected Text Boxes in  txt, max and pct Objects to retrieve values from these controls on the Report.

The last Property declaration in the global area is a Label control.  This will be used to draw an oval shape as legend symbol, along with another label with the caption Not Successful indicating what the same symbol appearing around the students  marks signifies..

The Property Get Procedure is actually not required in this Module and it is added here for completeness.  The Rpt Object is not at all accessed from outside this Module.

The Property Set Procedure receives the current Report Object through the Form_Load() Event Procedure as parameter and assigns it to the Rpt Object .

Next the Report Detail and Footer Sections are assigned to secRpt and secFutr Properties respectively.  Next two lines enables their Print Events to capture it when it happens on the Report.

Next three lines assigns the Text Box controls of the Report in txt, max and pct Text Box Properties declared in the global area of the module.

There is an empty label control in the Report Footer Section of the Report with the name Legend.  A Label Property is declared with the name lbl in the global area.  The Legend label is assigned in the lbl Property through the last statement Set lbl = Rpt.Controls("Legend") in the Set Property Procedure.

There are three Sub-Routines in the Class Module.  The Print Event when takes place in the Detail Section the secRpt_Print() runs, and when it happens in the Report Footer Section the sub-routine secFutr_Print() executes.  The third sub-routine DrawCircle() is called from both the above mentioned sub-routines to draw  an oval shape or ellipse around some of the Total Text Boxes in the Detail Section of the Report and on the Legend Label control in the Report Footer Section of the Report.

The Report Detail Section Print Event

When the Report's Detail Section starts printing (during Print Preview not Print View) the Print Event fires and secRpt_Print() sub-routines captures that Event and start executing the Code. 

The Text Box Property (max, pct and txt) values are read into m_max, pp and curval local variables.  The percentage of marks obtained by the student is calculated, up to two decimal places precision.  It is compared with the Pass Percentage (pp) and arrives at the logical result of Passed (TRUE) or Not Successful (FALSE) and saves the result in  Boolean variable yn.

Next, the DrawCircle() sub-routine is called, passing the value in yn as first Parameter and Total Text Box control as second parameter.

The DrawCircle() Sub-Routine.

The DrawCircle() subroutine first checks the first parameter received in Bool variable is TRUE or FALSE.  Accordingly a local Boolean variable bolPrintCircle  is set with the TRUE/FALSE Flag to signal the Circle drawing code segment to draw the oval shape or not to.

In this sample demo we have taken the failed student's cases to highlight the Marks with an oval shape around it.  So when the calculated marks percentage is below 60% then the yn Flag is set to False.  When the yn flag is False the bolPrintCircle is set to True to draw the circle around those values. 

The Text Box's positional values Left, Top and dimension Width and Height values are used to calculate the center point (Horizontal and Vertical point Coordinates) of the circle. Half measure of the Width Value of Text Box determines the Radius of the circle.

If the Text Box is too wide then the circle drawn in the Text Box will loose the top and bottom side of the circle, left and right side of the circle will appear as two Arcs.  The solution is to reduce the vertical radius of the circle (the Aspect Ratio), in relation to the actual radius value calculated based on the width of the Text Box. 

The vertical radius of the circle is reduced to the one-fourth of the horizontal radius or Aspect Ratio of the circle is set as sngAspect = 0.25.  The result is an oval shaped circle around the Text Box value. 

Aligning Text inside the Text Box

The Text Box value is horizontally text-aligned to the center of the Text Box.  But, vertically the value will normally appear near the top edge of the Text Box (and near the top edge of the circle too).  To shift the value down to make it appear somewhere in the middle of the oval shape  vertically, the Top Margin is set with the value 0.1cm manually in design view.  This Property value can be set only at design time, not through Code.

In the Report Footer Section there is a label control with the name Legend.   In the Report Footer_Print() Event calls the DrawCircle() sub-routine with the label control as parameter to draw an oval shape in the label control.  There is another label control with the Caption Not Successful, indicating what the oval shape around the marks of the student signifies.

Report Module Code

Option Compare Database
Option Explicit

Private R As New ClsStudentHighlight

Private Sub Report_Load()
  Set R.mRpt = Me
End Sub

The ClsStudentHighlight Class Module is instantiated in Object R.

On the Report_Load() Event Procedure the current Report Object is assigned to the Property R.mRpt.

Download the Demo database from the link given below and tryout the Report and Code.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
Share:

WithEvents and Report Line Hiding

Introduction

If you have tried out last week's Report OnPrint() Event trapping in Access Class Module then it will be easy for you here.  What we did there was Report's Detail Section Print  Event, when triggered, is captured in Class Module, validated the Field value and highlighted the Text Box and value of Exam passed students.  We are going to do something similar here too.

If we only need to highlight Text Boxes on the Report  with some color, change font-style Bold, Italic or Underline then we don't need the Print Event capturing in Class Module to do it through VBA Code. 

The Conditional Formatting method will do the highlighting of text, with different Color, Font Bold or Italic or Underline.  It only needs an expression like the following in conditional formatting dialogue box:  

Expression is [Total]/[MaxMarks]*100 >= [Percentage]

But, we can do many other things, which are not available in conditional formatting,  like changing the font or font size and highlighting border of the Text Box or any other styling through the Event handling in Class Module.

Last week we captured the Detail Section OnPrint() Event, but now we will try out the onFormat() built-in Event to hide certain report lines, leaving only the required ones (either passed or failed students list) on the Report.

We will use the same Report of last week, to prepare Passed or Failed Students' List from the same Report.

Sample Images of Report View, Print Previews

1.  Full list of Students in Report View (not in Print Preview) is given below.  Remember, on the Report View the Formatting and Printing actions takes place but those built-in Events are not fired on the Report View.  They are triggered only on the Print Preview mode.


2.  Passed Students' List in Print Preview, is achieved by preventing the failed students report lines from appearing on the Report.

3.  Failed Students' List in Print Preview and Passed Students report lines will not appear on the Report.

The Report option 2 and 3 are prepared without applying any filtering condition directly on the source data,  but showing or hiding the report lines in the Detail Section's Format Event does the job.

Class Module: ClsStudentsList VBA Code

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]

Private txt As Access.TextBox
Private max As Access.TextBox
Private pct As Access.TextBox
Private i As Integer

Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
Dim msg As String
Const strEvent = "[Event Procedure]"

  Set Rpt = RptNewVal

  With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnFormat = strEvent
  End With
  
  msg = "1. Passed List" & vbCr & "2. Failed List"
  i = 0
  Do While i < 1 Or i > 2
    i = Val(InputBox(msg, "Report Options", 1))
  Loop

  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("MaxMarks")
  Set pct = Rpt.Controls("Percentage")
End Property

Private Sub secRpt_Format(Cancel As Integer, FormatCount As Integer)
Dim curval As Double
Dim m_Max As Double
Dim m_pass As Double
Dim mk As Double
Dim pf As Double
Dim yn As Boolean
Dim lbl As Access.Label

On Error GoTo secRpt_Print_Err

m_Max = max.Value
pp = pct.Value
curval = txt.Value

pf = curval / m_Max * 100

yn = (pf >= pp)

Set lbl = Rpt.Controls("lblpass")

secRpt.Visible = False
       
       If yn Then
            txt.FontBold = True
            txt.FontSize = 12
            txt.BorderStyle = 1
            lbl.Caption = "Passed"
            lbl.ForeColor = RGB(0, FF, 0)
            lbl.FontBold = True
                If i = 1 Then
                    secRpt.Visible = True
                End If
        Else
            txt.FontBold = False
            txt.FontSize = 9
            txt.BorderStyle = 0
            lbl.Caption = "Failed"
            lbl.FontBold = False
                If i = 2 Then
                    secRpt.Visible = True
                End If
        End If

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print()"
Resume secRpt_Print_Exit
End Sub

In the global declaration area of the Class Module the Access.Report Property Rpt and Report Section Property secRpt are declared.

The three Text Box Control Properties txt, max and pct  are declared and will be assigned with references of the Text Box controls on the Report.  After that we can read Students Marks (txt), the Maximum Marks (max - of all Subjects) and Percentage (pct) of Pass Marks to calculate and categorize the Students as Passed or Failed.

The Integer Type Property i will hold the Report Option 1 or 2 (i.e. 1. Passed Students List. 2. Failed Students List.) entered by the User when the Report is Run.

The Property Set mRpt() Procedure assigns the current Report Object through the Report_Open() Event Procedure and assigns the RptNewVal Parameter value to the Rpt Object.

Immediately after assigning the Report to Rpt Object the Report Detail Section is assigned to the secRpt Object.  The Report Detail Section Format Event triggering mechanism is also enabled within the following Code segment in the Set mRpt() Property Procedure.

With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnFormat = strEvent
  End With

Why we can't use last week's OnPrint() Event here, to hide the required Report lines, because Print Event is the final phase of other actions like Formatting phase. Once the Formatting phase is complete and Printing starts we cannot hide individual report lines.  If the statement secRpt.Visible = False is executed at this point. The entire Detail Section will not be visible.

Report Events before Viewing/Printing.

  1. In the first pass of Report Formatting the Format Event fires and determines which data belongs to the Report Page.  This is where our programmed activities takes place.
  2. In the second formatting pass the Report lines are formatted for printing/previewing.  
  3. The Print Event fires immediately after the second formatting pass but before the Report lines are printed on Detail Section.

Within the Do while . . . Loop we have inserted an Inputbox() statement to get the user response (1 or 2) for report option: 1. Passed Students List, 2. Failed Students List.

The User is forced to enter the value 1 or 2. Any value outside this range is not accepted and the Do while . . . Loop keep repeating itself till a valid value is received in the Variable i.

Next three statement assigns the Text Box controls to their respective declared Properties.

When the Report is open in the Print Preview Mode the Detail Section Format Event is fired and getting captured in the secRpt_Format() sub-routine in the Class Module: ClsStudentLine. Few local variables are declared at the beginning of the sub-routine.

The Maximum Marks and Pass Percentage Text Box values are read into max and pct. The curval = txt.value statement reads the students obtained Total marks and assigns it to the Variable curval.

In the next line the statement pf = curval / m_Max * 100 calculates the percentage of marks obtained by the student.

yn = (pf >= pp) - compares the obtained percentage of marks with the Pass Percentage and checks whether the obtained percentage is greater than or equal to Pass Percentage, if so the result in Variable yn = TRUE (Passed) otherwise yn = FALSE (Failed).

The lbl Control is set with the Label control reference, of the Label to the right of the Total Text Box control.

Next Line the Report Detail Section is first kept in hidden state. When the Student is found in Passed category (yn = TRUE) then the Total TextBox is formatted to highlight it and the Label Control's Caption is set with the text Passed. If yn = FALSE then the Formatting is reset to normal and the label control is set with the text Failed, depends on what type of Report you have opted for.

Based on the Report Option selected by the user in response to the InputBox() Function.

The statement i=Inputbox() within the Do While . . . Loop  gives the user two choices to select: 1.  Passed Students List, 2.  Failed Students List.

How it works.

Option 1: If the student, in the current line on Detail Section is found passed the Exam then the Detail Section is made visible.  That particular report line appears on the Report.  This check is done on each line of Report and passed students only are made visible on the Report. 

Option 2: Detail Section is made visible for Failed student data lines, hiding others.

Report Class Module Code.

The Report Class Module Code is given below:

Option Compare Database
Option Explicit

Private R As New ClsStudentsList

Private Sub Report_Open(Cancel As Integer)
  Set R.mRpt = Me
End Sub

On the Report Code Module the Class Module ClsStudentList  is instantiated in Object R.  In the Form_Open() Event Procedure the current Report Object is passed to the Class Object's R.mRpt() Set Property Procedure. These are the only Code required on the Report's Class Module.

All the other actions are happening behind the curtain in the Class Module ClsStudentsList.

Note: Always open the Report in Print Preview Mode (not in Report View) to trigger the Format Event in the Detail Section of the report.

You may Download the Demo Database from the Link given below and try out the Report and Code.


  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
Share:

WithEvents and Access Report Event Sink

Introduction

We have tried out several WithEvents examples with Class Modules, capturing built-in Events and User-defined Events from Form based Controls.  We have created Class Module Object Arrays for several Text Box Controls on Form. We have also tried creating separate Class Object instances for each Text Box on the Form or sub-forms and added them  to Collection Object Items.  We found both the above methods (Arrays or Collection Object Items) works well in capturing Events Raised from Form Text Boxes and in executing the Event handling sub-routines. 

There are other control types to try out with Text Box controls on the Form, when several of them present on the same Form. We will definitely take them up little later.

After lengthy trial runs with Text Box Controls on Form need some change of scene now, away from Access Forms for the time being, and go for the Microsoft Access Reports for few simple Event handling demo runs.

Preparations

Need the following Objects for the trial run:

  1. Table with Students Name and Marks.
  2. Report designed with the Students Table.
  3. Class Module to handle the Report Print Event.

We need a sample Table with few Students name and their exam's marks total. Our task is to highlight the passed student's mark and update a Label control, to the right of their marks with the Caption value passed .

Image of sample Table: Students

Sample Report designed using the above Table and the sample image of the Report is given below, without running the Event Procedures.

Report Image Contents

The Report Detail Section shows the Table records, employee name and total marks obtained.  On the Report Header area some extra information is inserted to explain how the Pass Mark percentage of student is calculated.  The Maximum Marks of all subjects is 600.  Students with 60% or more marks are in the Passed category.

There is a Text Box on the Header Section, to the extreme right area, with the header label Set Pass %The user can set a different percentage parameter value expressed in whole number format, currently set with the value 65.  Based on this value the Print Event Procedure will calculate the obtained marks percentage and highlight the qualified student's marks. A label control with the Caption Passed will appear to the right of the Marks.

The sample Report image after executing the Event Procedure, highlighting passed students Marks, is given below:

Class Module Code

The Class Module: ClsStudents VBA Code that handles the Report Detail Section Print Event is given below:

Option Explicit

Private txt As Access.TextBox
Private pct As Access.TextBox
Private max As Access.TextBox

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]

Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
Const strEvent = "[Event Procedure]"

  Set Rpt = RptNewVal
  With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnPrint = strEvent
  End With
  
  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("Maxmarks")
  Set pct = Rpt.Controls("Percentage")

End Property

Private Sub secRpt_print(Cancel As Integer, printCount As Integer)
Dim curval As Double
Dim m_Max As Double
Dim pf As Double
Dim pp As Double
Dim lbl As Access.Label

On Error GoTo secRpt_Print_Err

Set lbl = Rpt.Controls("lblPass") 'set reference to Label: lblpass

m_Max = max.Value 'retrieve Maximum Marks (600)
curval = txt.Value 'get current Report Line 'Total' TextBox value
pp = pct.Value 'get the percentage value (65)

pf = curval / m_Max * 100 'calculate obtained marks percentage

If pf >= pp Then 'if it is greater or equal to 65
    txt.FontBold = True
    txt.FontSize = 12
    txt.BorderStyle = 1
    lbl.Caption = "Passed" 'change label caption to 'passed'
Else 'reset to normal
    txt.FontBold = False
    txt.FontSize = 9
    txt.BorderStyle = 0
    lbl.Caption = ""
End If

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print()"
Resume secRpt_Print_Exit
End Sub

Let us have a brief introduction to what is going on within the above Class Module.

Out of the top three Text Box control declarations in the Global Area of the Class Module the first txt Property will be assigned with the Marks of each student from the Detail Section of the Report, during the line by line Printing phase of the Report.

The next two Text Box Properties max and pct will be set with direct references of the Text Boxes MaxMarks & Percentage on the Header Section of the Report.  These will be assigned with the values Maximum Marks and Percentage, sample values 600 and 65 respectively.  These will be used in calculations to find the percentage of marks of each student.

The first of the next two lines declares the Access.Report Property Rpt and the second line defines Property secRpt for the Report's Detail Section.

The only Get/Set Property Procedures in the Class Module are for the Report Object and assigns the active Report Object from the user Report's Class Module.

Once the Report reference is received in the Set Property Procedure the Report Detail Section is assigned to the SecRpt Property and the Report_Detail_Section_OnPrint() Event is enabled through the following statements:

Set Rpt = RptNewVal
  With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnPrint = strEvent
  End With

The next three statements sets the references of the Text Box Controls on the Report to the txt, pct & max Properties, declared at the top of the Class Module.

Before the Report is shown to us, in Print Preview or sent to Printer, the Report Page Lines undergoes several formatting passes by the System to lay out the Page contents line by line and finally Prints it line by line on the Report Page.  This Print action is the final phase in the preparation of a Report Page.

The Report.Section(acDetail).OnPrint() Event

We are only interested in the Report's Detail Section Print Event and captures it in secRpt_Print() sub-routine, when it happens.  The Total marks of the student is saved in curval Variable by the expression curval = txt.value.  Calculations are made to arrive at the percentage of marks obtained by the student out of 600 and compares it with the pass percentage given on the Report Header. When the student is found passed then his/her Total marks Text Box is highlighted.  The Text Box border will be highlighted, the font-size will be increased to 12 points and set with Bold style.  A label control to the right of the Text Box will appear with the Caption: passed.

The Report_Students Class Module Code is given below.

Option Compare Database
Option Explicit

Private R As New ClsStudents

Private Sub Report_Open(Cancel As Integer)
  Set R.mRpt = Me
End Sub

The Class Module ClsStudents is instantiated in Class Object R.

On the Report_Open Event the current Report Object is passed to the Set Property Procedure Set R.mRpt().

Important Points to Note

Once you complete designing and setting up the Report as explained above it is time to view the Report Contents with the Print Event sink action in Class Module Object.  There are different Views for an Access Report, besides Design View:

  1. Layout View
  2. Report View
  3. Print Preview

The Report or Report Section onPrint or Format Event will not fire on the first two Report Views. 

You can find the Report with Data and the way you designed it.  But, you will not find the result of your Event Procedure running in the Class Module, if you use the first two methods.

In that case use the following methods:

  1. Right-click on the Report in the navigation pane and select Print Preview from the displayed Menu.
  2. If you double-clicked on the Report in the navigation pane and you ended up in Report view mode then right-click on an empty area on the Report View and select Print Preview from the displayed Menu.

If you are not using Access 2007 Version then always try to select the Print Preview Mode through whatever option available there, to Raise the Report_Print or Report_Format Event.

Summary

The active Report Detail Section onPrint Event is enabled from within the Set mRpt() Property Procedure of ClsStudents Class Module.   The Event when Raised on the Report it is captured from within the Class Module itself in the Private Sub secRpt_Print() sub-routine.  The data in each line in the Detail Section of the Report is Validated and if found qualified then highlights the Text Box in the Report Print Preview. 

All actions are executed from within the Class Module leaving the Report Class Module nearly empty except four lines of Code.

The Demo database is attached.  You may download and try out and study the code.  Try out something similar on your own for self test.  You may use the demo database as a reference point when you are in doubt with the Syntax or some other issues.

Next week we will try how to take Printout of Passed Students alone on the Report without creating a Query to filter the Data.  I will give you clue, we will hide the failed students lines from the Report detail Section.



  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
Share:

WithEvents in Class Module and Data Entry Form

1. Introduction.

So far we have used Unbound Text Boxes on Form for Demo runs, for built-in Event capturing in Class Module Object.  Since, Text Boxes are the main control used on Forms, for data entry/display/view of information, it was the first preferred choice for demonstration of most frequently used built-in Events: AfterUpdate, OnGotFocus and OnLostFocus.

That doesn’t mean that we have ignored Command Buttons, Combo Boxes, List Boxes and others.   All these controls mostly uses the Click Event, to open Form/Report or run macros/sub-routines/functions,  select an item from  Combo box or List Box or Option Button and they are much easier to handle in their Event Procedures in the Class Module.  We will take those controls also along with Text Boxes

Once you are familiar with handling the Text Box based Events alone and understand how it works then other controls will be a simple addition to the whole scheme of things.

Adding Class Module Object instance, one instance for each Text Box on the Form, into the Array element, enabled with required built-in Events itself is somewhat confusing, but not complicated.

If you have not touched the topic of programming the stand alone Class Module I suggest you better start from there.  It will help you a lot and much easier to understand it’s usage in WithEvents, Event and RaiseEvent  programming.  Go through the following Articles to get a general idea of Class Module Programming.

  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

2. Brief Review of what is covered so far.

Last few weeks we have started with the topic of Class Module and Form Controls Event Programming in Microsoft Access and  learned how to create Class Module Object Array element for each Text Box on Form.

The preparations for creating a Class Module for Text Box object is something like the following steps.

  1. Creates a Class Module with the Text Box (Access.TextBox) Property with Object (or Variable) name like txt.
  2. If the Property is declared as Private then Get/Set Property Procedures are added to manage the txt Object.
  3. Event Handling sub-routines for AfterUpdate, GotFocus and LostFocus are created in the Class Module.

When an instance of the above Class Module is created in memory  it can handle only a single Text Box’s Events (AfterUpdate, GotFocus and LostFocus) on the Form.

If there are more Text Boxes, say three Text Boxes, on the Form then we must create three instance of the same Class Module, one for each Text Box on the Form. 

To keep track of all the three instances of the Class Module they should be organized in an Array  or add them as Collection Object Items, one after the other.

When the same Class Module instance is used for more than one Text Box the Event Procedures (sub-routines) need changes in the Class Module.  We must identify the Text Box that Raised the built-in Event on the Form.  Based on that information run the validation checks or any other action on that particular Text Box within the same sub-routine.

Example: The Code given below is taken from the AfterUpdate() Event Procedure. The FirstName and Designation Text Box’s AfterUpdate Event don't have any executable Code under them, in the Select Case . . . End Select structure, indicating that these Text Boxes exists on the Form but the AfterUpdate Event are not enabled or used for them.  If there is no executable code in them it is not necessary to add the Text Box name there.  But, adding those fields as part of the testing sequence reminds us their presence on the Form.

But these Text Boxes may be enabled with some other Event like OnLostFocus and handled in the LostFocus Event Procedure.

txtName = Txts.Name

Select Case txtName
    Case "LastName" 'TextBox Name
        txtval = Trim(Nz(Txts.Value, ""))

        If Len(txtval) > 15 Then
           msg = "LastName Max 15 chars only."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Left(Nz(txtval, ""), 15)
        End If
    Case "FirstName"
        '
    Case "Designation"
        '
    Case "BirthDate"
         db = CDate(Nz(Txts.Value, 0))
        
         If db > Date Then
           msg = "Future Date: " & db & " Invalid."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Null
           efrm!Age = Null

         ElseIf db > 0 Then
           dbage = Int((Date - db) / 365)
           efrm!Age = dbage
         End If
    Case "Age"
         Dim xage As Integer
         db = CDate(Nz(efrm!BirthDate, 0))
         xage = Nz(Txts.Value, 0)
         
         If (db > 0) And (xage > 0) Then
            dbage = Int((Date - db) / 365)
            If xage <> dbage Then
                msg = "Correct Age as per DB = " & dbage
                MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
                Txts.Value = dbage
            End If
         ElseIf (xage = 0) And (db > 0) Then
            dbage = Int((Date - db) / 365)
            Txts.Value = dbage
         End If
    Case "JoinDate"
       Dim jd As Date
       
       db = CDate(Nz(efrm!BirthDate, 0))
       jd = CDate(Nz(Txts.Value, 0))
       
       If (db > 0) And (jd < db) Then
          msg = "JoinDate < Birth Date Invalid!"
          MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
          Txts.Value = Null
       End If
End Select

Note: If you need to Read/Write value from/to some other Text Box, other than the Event trapped Text Box, then you need an Access.Form Property also in the Text Box Class Module: ClsTxtEmployees, to reference the other Text Box to read/write values.

This Class Module can be a Template for other Form’s Text Boxes and customization of sub-routines are also required depending on that particular Form's Text Box control's  requirement.

The links of all the earlier Articles on this topic are given below, if you are new to the WithEvents, Events and RaiseEvents usage in User-Defined Events or built-in Event Programming in Microsoft Access, for easy access to those articles.


  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events

3. What is New in this Post.

Here, we have a sample Employees Data Entry Form bound to a Table.  If you have gone through the earlier examples with Text Boxes on unbound Form you will not find any difference in this Demo.  The only idea here is to demonstrate the normal usage of Event handling in Class Module Object with the form bound to a Table and the data fields as control source to the Text Box.

The sample image of the Data Entry Form is given below.  An error message shown on the image is popped up (this is manually moved to the bottom right corner) when an invalid JoinDate (less than birth date) is entered into the field.

The Command Button Click on the form is handled normally on the Form Module itself in the first Employee Form Demo.

On the Employee Form demo the Form’s Class Module and a Class Module with two Private Properties, Txts  for Text Box Object  and efrm for Access.Form Object are used.

4.  New Class Module Code.

Insert a new Class Module and change it's Name Property value from Class1 to ClsTextEmployees.

Copy and Paste the following VBA Code into the Class Module and save the Code:

Option Compare Database
Option Explicit

Private efrm As Access.Form
Private WithEvents Txts As Access.TextBox

Public Property Get pfrm() As Access.Form
  Set pfrm = efrm
End Property

Public Property Set pfrm(ByRef vNewValue As Access.Form)
  Set efrm = vNewValue
End Property

Public Property Get pTxts() As Access.TextBox
  Set pTxts = Txts
End Property

Public Property Set pTxts(ByRef vNewValue As Access.TextBox)
  Set Txts = vNewValue
End Property

Private Sub Txts_AfterUpdate()
Dim txtName As String, txt As String
Dim msg As String, txtval As Variant
Dim db As Date, dbage As Integer

txtName = Txts.Name

Select Case txtName
    Case "LastName"
        txtval = Trim(Nz(Txts.Value, ""))

        If Len(txtval) > 15 Then
           msg = "LastName Max 15 chars only."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Left(Nz(txtval, ""), 15)
        End If
    Case "FirstName"
        '
    Case "Designation"
        '
    Case "BirthDate"
         db = CDate(Nz(Txts.Value, 0))
        
         If db > Date Then
           msg = "Future Date: " & db & " Invalid."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Null
           efrm!Age = Null

         ElseIf db > 0 Then
           dbage = Int((Date - db) / 365)
           efrm!Age = dbage
         End If
    Case "Age"
         Dim xage As Integer
         db = CDate(Nz(efrm!BirthDate, 0))
         xage = Nz(Txts.Value, 0)
         
         If (db > 0) And (xage > 0) Then
            dbage = Int((Date - db) / 365)
            If xage <> dbage Then
                msg = "Correct Age as per DB = " & dbage
                MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
                Txts.Value = dbage
            End If
         ElseIf (xage = 0) And (db > 0) Then
            dbage = Int((Date - db) / 365)
            Txts.Value = dbage
         End If
    Case "JoinDate"
       Dim jd As Date
       
       db = CDate(Nz(efrm!BirthDate, 0))
       jd = CDate(Nz(Txts.Value, 0))
       
       If (db > 0) And (jd < db) Then
          msg = "JoinDate < Birth Date Invalid!"
          MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
          Txts.Value = Null
       End If
End Select
End Sub

Private Sub Txts_LostFocus()
Dim txtName As String
Dim msg As String, txtval As Variant

txtName = Txts.Name
txtval = Trim(Nz(Txts.Value, ""))

Select Case txtName
    Case "LastName"
        '
    Case "FirstName"
        If Len(txtval) = 0 Then
           msg = "FirstName should not be Blank."
           MsgBox msg, vbInformation, txtName & "_LostFocus()"
           Txts.Value = "XXXXXXXXXX"
        End If
    Case "Designation"
        If Len(txtval) = 0 Then
           msg = "Designation Field is Empty."
           MsgBox msg, vbInformation, txtName & "_LostFocus()"
           Txts.Value = "XXXXXXXXXX"
        End If
    Case "BirthDate"
        '
    Case "Age"
        '
    Case "JoinDate"
        '
End Select

End Sub

5. Class Module Properties and Sub-Routines.

On the Global area of the module an Access.Form Property efrm declared as Private Property.

Next, an Access.TextBox control is declared, with WithEvents keyword and object name Txts as Private PropertyThe WithEvents keyword enables the Txts Object to capture the programmed Events when triggered on the Form. 

When Class Module Properties are declared as Private, it is accessible only within the Class Module, preventing direct access to the Objects from outside, there must be Get/Set Property Procedures with Public Scope in the Class Module to assign/retrieve values to/from it.

The first Get/Set Property Procedure pairs are for the efrm Access.Form Object.

The next set of Get/Set Property Procedure pairs retrieve/assign TextBox Control in Txts Object.

Any validation checks on the Set Property Procedure parameter can be performed before attempting to assign the value to the Property efrm or Txts.

In the Class Module we are handling only two type of built-in Events, AfterUpdate and LostFocus Events from Text Boxes on the Form.

The AfterUpdate() Event Procedures validates the Values of LastName, BirthDate, Age and JoinDate Fields and displays an appropriate message, mainly to monitor whether the programmed Event is getting captured in the Class Module instance or not.

The LostFocus() Event Procedure validates the FirstName and Designation field values and assigns a default text String “XXXXXXXXXX” to the field, if it is left empty during the Lost Focus Event.

All the Text Box names are listed in the Select Case . . . End Select structure for clarity, some without any executable code in it.  In the LostFocus Event Procedure we have written code under the FirstName and Designation fields.  Lost Focus event is enabled on the Form only for these two data fields.  Other fields are included for clarity or for future use in case if they are needed.

The first Text Box on the Form is an Auto-number field, increments it's contents automatically and we have no intention to trap any events on this particular control.  Hence, it is not at all taken in the Select Case . . . End Select structure, or enabled any Event on that control either.

6.  Employees Form Module Code.

The VBA Code behind the Employees Form's Class Module is given below:

Option Compare Database
Option Explicit

Dim tc As ClstxtEmployee
Dim C As Collection

Private Sub cmdClose_Click()
'Command Button Click Event is handled
'on the Form Module itself, the Event is
'not programmed in Class Module: ClsTxtEmployee
DoCmd.Close
End Sub

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
  
     Set tc = New ClstxtEmployee
     
     'Form Object is required to read/write values
     'from other TextbOX, if needed.
    Set tc.pfrm = Me
    'assign TextBox control to the Class Module instance's Property
    Set tc.pTxts = ctl
    
    Select Case ctl.Name
          Case "FirstName", "Designation"
          'enable LostFocus Event for FirstName and Designation
               tc.pTxts.OnLostFocus = "[Event Procedure]"
          Case Else
           'enable AfterUpdate for all other Text Boxes
               tc.pTxts.AfterUpdate = "[Event Procedure]"
    End Select
  End If
  C.Add tc 'add ClstxtEmployee instance as Collection Object Item
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
'when the form is closed erase Collection Object from memory
Set C = Nothing
End Sub

The Class Module ClsTxtEmployee is declared as tc Object.

A Collection Object is declared in Object C.

On the Form_Load() Event Procedure the Collection Object is instantiated.

Within the For Each . . Next loop the Employees Form Text Box controls are picked and enabled the required built-in Events.

For each text box on the Form Employee a new instance of the Class Module ClsTxtEmployee is created and the Form Object and Text Control Property Values are passed to the Class Module Object.

7.  Derived Class Module to Replace Form Module Code.

As we did in the earlier cases we will create a Derived Class Object (ClsTxtEmployeeHeader), using ClsTxtEmployee Class as Base Class and transfer above Form Module Code into the new Class Module and leave the Form's Class Module with the bare minimum of few lines of Code.

We will also transfer the Command Button Click Event handling into the Derived Class Module Object.

The Derived Class Module (ClsTxtEmployeeHeader) VBA Code is given below:

Option Compare Database
Option Explicit

Private tc As ClstxtEmployee
Private Col As New Collection

Private fm As Access.Form
Private WithEvents btn As Access.CommandButton


Public Property Get oFrm() As Access.Form
  Set oFrm = fm
End Property

Public Property Set oFrm(ByRef vNewValue As Access.Form)
  Set fm = vNewValue
  Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In fm.Controls
  Select Case TypeName(ctl)
  'If TypeName(ctl) = "TextBox" Then
    Case "TextBox"
        'create a new instance of Class Module Object
        Set tc = New ClstxtEmployee
        'assign common property values
        Set tc.pfrm = fm 'pass Form Employyes object to the new instance
        Set tc.pTxts = ctl  'pass text control
            
            'enable required event procedures for Text Boxes
            Select Case ctl.Name
                   'lostfocus event controls
                Case "FirstName", "Designation"
                    tc.pTxts.OnLostFocus = "[Event Procedure]"
                Case Else
                    'after Update Event
                    tc.pTxts.AfterUpdate = "[Event Procedure]"
            End Select
            Col.Add tc 'add to the collection object
    Case "CommandButton"
        Set btn = ctl
        btn.OnClick = "[Event Procedure]"
    End Select
Next

End Sub

Private Sub btn_Click()
   If MsgBox("Close the Form?", vbYesNo, btn.Name & "_Click()") = vbYes Then
    DoCmd.Close acForm, fm.Name
   End If
End Sub

In the above derived Class Module the first two object declarations for Text Box Class Module and Collection Object we have done on the Employees Form's Class Module is brought down to this new Class Module.

Next, an Access.Form object fm is declared to hold the Employees form object. This is required to read the date of birth (the BirthDate) Text Box and calculate the Age of the employee and update the value in the Age Text Box on the Form.

Next, a Command Button control Object is declared within the derived Class Module Object with the WithEvents key word to take control of the Command Button click on the Form.

Once the Form Object's reference is received in the Public Property Set oFrm() as parameter and after assigning it to fm Object  the Class_Init() Subroutine is called to run the Code to enable the AfterUpdate and LostFocus Event, earlier we run from the Form_Load() Event Procedure.

Now the only Code we run from the Form Class Module, on the Form_Load() Event Procedure, is to pass the reference of the current Form Object (Me) to the oFrm() Set Procedure in the derived Class Module Object ClsTxtemployeeHeader.  This Form reference is passed to the ClsTxtEmployee Class Module instance, through the statement  Set tc.pfrm = fm.

The Command Button Click Event Procedure is enabled and captured in the btn_Click() Event Procedure in the Derived Class Module ClsTxtEmployeeHeader itself.

8.  New Form using Derived Cass Module: ClsTxtemployeeHeader

Image of the second sample form, after transferring all it's Form Module Code into the Derived Class Module ClsTxtEmployeeHeader.

The EmployeeHeader (a copy of the Employees Form) Form's Class Module VBA Code is given below.

Option Compare Database
Option Explicit

Dim T As New ClsTxtEmployeeHeader

Private Sub Form_Load()
  Set T.oFrm = Me
End Sub

The Dim statement instantiates the derived object of ClsTxtEmployeeHeader in memory.

The Form_Load() Event Procedure passes the current form object to the class object T.oFrm() Property Procedure as it's parameter.

9. Summary

The Derived Class Module holds the entire Form's Class Module Code, except a few lines on the Form's Code Module.

The entire Code written, otherwise on the Form's Class Module is away and safe in the Base Class Module ClsTxtEmployees and in the Derived Class Module ClsTxtEmployeeHeader. 

When you create another Form with features of similar nature, in this Project or in your other Projects you can easily customize both the the Class Modules.  This will keep things well organized and easy to implement form rather than writing fresh code on all Form's Class modules.

You may download the Demo Database attached at the end of this Page with the sample Forms and Class Modules and try out them.

After going through them you may try something similar on your own, using the demo database as a reference point so that you can be sure what you have learned so far.


Share:

WithEvents in Class Module for Sub-Form Text Box Events

Introduction.

So far we have used only a single Form and Text Box Controls on it to demonstrate the usage of WithEvents and built-in Event capturing, running subroutines in Class Object Arrays or in Collection Object items.  But, how do we write code for Sub-Form(s) based Text Box controls, of the Main Form, to invoke built-in Events and to capture them in Class Module Objects.

The sample form image with two Sub-Forms and few Text Boxes on all the Forms are given below for reference.

We will be using the familiar AfterUpdate or  OnGotFocus or OnLostFocus Events on Text Boxes on Main Form and Sub-Forms.  But let us concentrate on one or two points we have not yet dealt with so far.

  1. Introduction of the Access.Form Property in the Class Module of Text Box Control.
  2. How to address the Text Box Controls on the Sub-Form, to invoke the built-in Events and capture them in the Class Module?
  3. How to move the insertion point from the last Text Box on the first Sub-Form to the first Text Box on the second sub-form?
  4. How to move the insertion point from the last Text Box on the second sub-form to the only text box on the Main Form?
  5. How to use the reference of other controls to read/write on the Form control, from within the current Event Procedure in the Class Module?

Why Form Property Required in Class Module.

So far we have worked with a single Text Box Property in the Class Module .  The Event Procedures are also mostly deals with the Text Box Name and it’s Value alone. While working with the value of the current text box, that triggered the Event, there are situations where we need the value from other Text Box controls on the Form.

For example: We update the employee’s date of birth on one text box, we need to find  the employee’s retirement date, at the age of 56 years and update it in another Text Box.

Option 1: At the after-update event procedure of the date-of-birth text box, calculate the retirement date and update it into the retirement date text box directly.

Option 2: After updating the date-of-birth Text Box the cursor jumps to the Retirement Date control.  On the OnGotFocus Event on this text box read the date-of-birth value from the Birth-Date text box, calculate the retirement date and insert it into the current text box.

In either case you have to work with two text boxes to read or write value into it,  To read/write value into the other text box we can reach it only through the Form Object. To do that we need the Access.Form Object as Property in the Class Module, besides the Access.TextBox control Object.

The new Class Module (ClsSubForm) Code is given below:

Option Compare Database
Option Explicit

Public WithEvents txt1 As Access.TextBox
Private frm2 As Access.Form

Public Property Get sFrm() As Access.Form
  Set sFrm = frm2
End Property

Public Property Set sFrm(ByRef frmValue As Access.Form)
  Set frm2 = frmValue
End Property

Private Sub txt1_AfterUpdate()
Dim ctl As Control, ctlName As String
Dim bd As Date

ctlName = txt1.Name
Select Case ctlName
    Case "EmpName"
      If Len(Nz(txt1.Value, "")) > 10 Then
         txt1.Value = Left(txt1.Value, 10)
         MsgBox "Maximum 10 characters only allowed.", vbInformation, ctlName & "_AfterUpdate()"
      End If
    Case "BirthDate"
      bd = CDate(Nz(txt1.Value, 0))
      If bd > 0 And bd < Date Then
         MsgBox "BirthDate Valid:" & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
      End If
    Case "Email"
       MsgBox "Email Address: " & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
    Case "Mobile"
       MsgBox "Mobile No. " & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
End Select

End Sub

Private Sub txt1_GotFocus()
'Got Focus Event is set for only
'Retirement Date field
'Testing for the field name is not required
Dim vDate As Variant, rdate As Date
Dim tmpRef As Access.Form

   Set tmpRef = frm2.frmSubForm1.Form
   'vDate = frm2.frmSubForm1.Form!BirthDate
   'vDate = frm2.frmSubForm1.Form.Controls("BirthDate").Value
   vDate = tmpRef!BirthDate
   
If vDate > 0 Then
    vDate = CDate(vDate)
    'calculate retirement Age/Date
    rdate = vDate + Int(56 * 365.25)
   txt1.Value = rdate
End If
MsgBox "Retire Date: " & txt1.Value, vbInformation, txt1.Name & txt1.Name & "_GotFocus()"
End Sub

Private Sub txt1_LostFocus()
Dim txtname As String
txtname = txt1.Name
Select Case txtname
    Case "RetireDate"
         'frm2.Controls("frmSubForm2").SetFocus
          frm2.frmSubForm2.SetFocus
    
    Case "Email"
          MsgBox "Email Address: " & txt1.Value, vbInformation, txtname & "_LostFocus()"
    Case "Mobile"
          MsgBox "Mobile Number: " & txt1.Value, vbInformation, txtname & "_LostFocus()"
          'frm2.Controls("EmpName").SetFocus
            frm2.EmpName.SetFocus
    Case Else
        If CDate(Nz(txt1.Value, 0)) = 0 Then
            MsgBox "Birth Date is Empty!", vbInformation, txtname & "_LostFocus()"
        End If
End Select
End Sub

Class Module Properties and Sub-Routines.

The TextBox Property is declared, with the WithEvents keyword, as txt1 Object, with Public Scope . Even though we are breaking the rule of encapsulation, by declaring this property as Public, it is justified because we are in the learning curve.  Later on you can declare it as a Private Property and add the Get/Set Property Procedures and change the Code to address the txt1 Object Property through the Property Procedures.

The Form Object frm2 will be assigned with the Main Form (Form_frmMain with it’s sub-forms) Object so that we can retrieve values from sub-form based text box controls, or write values into them too.

The txt1_AfterUpdate() Event Procedure in the ClsSubForm Class Module doesn't have anything new in it, because we saw it’s performance before.  The BirthDate, Email and Mobile  Text Box controls are from both sub-forms on the Main Form.  The AfterUpdate Events triggered on the Sub-Form controls are also comes into this Sub-Routine.

The txt1.GotFocus() Event Procedure of RetireDate Text Box runs in this Sub-Routine.  To insert the employee’s retirement age/date we need to read the Date of Birth of the employee from the BirthDate Text Box.  To read the value from there we need the direct reference of this Text Box on the first sub-form, using the frm2 Main Form Object Property.

Referencing Sub-Form Text Box from Class Module.

Different versions of the BirthDate Text Box references are given below:

vdate = frm2.frmSubForm1.Form!BirthDate
vDate = frm2.frmSubForm1.Form.Controls("BirthDate").Value

Or take the long route:

Dim tmpRef As Access.Form
Set tmpRef = frm2.frmSubForm1.Form
vDate = tmpRef!BirthDate

Now, stepping into the txt1.LostFocus() Event Procedure the notable actions we have included here is transferring control (or setting the insertion point) from frmSubForm1 last Text Box to frmSubform2 first Text Box and frmSubform2 last Text Box to the only Text Box on the frmMain form.

  1. Moving the insertion point from the last Text Box on frmSubForm1 to the first Text Box on  frmSubForm2.
    frm2.frmSubForm2.SetFocus
    OR
    frm2.Controls("frmSubForm2").SetFocus
    
  2. Moving the insertion point from the last Text Box on Sub-form2 to the EmpName Text Box on the Main Form.
    frm2.EmpName.SetFocus
    OR
    frm2.Controls("EmpName").SetFocus
    

Common Pitfalls in Setting Focus on Sub-Form Control.

The pitfall that we often encounter in attempting to set the focus on a Text Box inside the sub-form directly is something like the following example:

frm2.frmSubForm2.Form.Email.SetFocus

The above statement is logically correct and will not give any error messages when run, but it will not work.  The point to note here is that a sub-form sits within a sub-Form Container  control (with the same name of the Sub-Form) and setting focus on this control is enough.   When we do that the Text Box or any other Control on the sub-form with Tab Index 0 will automatically become active or will receive focus.

This is what we did in the first example, the reference of the sub-form container control on the Main Form (frm2.frmSubForm2.SetFocus) is used to set the focus on.  When you add the .Form  to the sub-form container-control then it become the reference of  a Form and the system simply ignores it.

The second option frm2.Control(“frmSubForm2”).Setfocus explicitly addressing it as a Control of the Main Form: frm2.

Setting Focus on a control on Main Form from the sub-form is straight forward frm2.EmpName.SetFocus or frm2.Controls(“EmpName”).Setfocus.

Building the Derived Class Module: ClsSubFormHeader.

Armed with enough ground work information on changes in the ClsSubForm Class Module we will proceed to build the Header Class (or Derived Class ClsSubFormHeader) to add the Form/Sub-Form Text Box Controls to the Collection Object.  We will add appropriate built-in Event statements required to announce the Event Procedure execution on the Text Boxes.  Without that we cannot be sure that the Class Module actually does the job we assigned to it, since the actions are happening behind the curtain, not on the Class Module of the Form.  Once you make sure that everything works fine you can remove the unwanted messages, showing the values from the Text Box.

The point to note here is that how to address (setting the reference to the Text Box controls on the sub-form(s) while adding it to the Collection Item.

Separate Class Object Instance for each Form Control

Remember this, separate instance of the Class Object ClsSubForm is added, for each Text Box on all the Forms, as separate Item in the Collection Object.  The Text Box’s required built-in Event triggering mechanism is also enabled in the Class Object, before adding it to the Collection Object.  Even though the Form’s Code Module is empty our Class Module Object is continuously monitoring, for the enabled built-in Event to happen, from the Collection Object storage.  When it happens on a particular Text Box the related ClsSubForm Class Object instance based sub-routine, like txt1_AfterUpdate(), executes and displays it’s result in the form of a message in the Application Window, or updates the value in a Text Box on the Form. 

The ClsSubFormHeader Class Object Code is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private T As ClsSubForm
Private C As New Collection

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(frmVal As Access.Form)
  Set frm = frmVal
  Call Class_Init
End Property

Private Sub Class_Init()
Dim cnt As Integer
Dim ctl As Control

'Scan for TextBoxes on the Main Form
For Each ctl In frm.Controls
   If TypeName(ctl) = "TextBox" Then
   
       Set T = New ClsSubForm ‘instantiate Class
       Set T.txt1 = ctl
       
       Select Case ctl.Name
           Case "EmpName"
               T.txt1.AfterUpdate = "[Event Procedure]"
       End Select
       C.Add T ‘add to collection object
   End If
Next

'Scan for TextBoxes on the Sub-Form
For Each ctl In frm.frmSubForm1.Form.Controls
   If TypeName(ctl) = "TextBox" Then
   
      Set T = New ClsSubForm ‘instantiate Class
      Set T.txt1 = ctl
      Set T.sFrm = frm
      
       Select Case ctl.Name
           Case "BirthDate"
                  T.txt1.AfterUpdate = "[Event Procedure]"
                  T.txt1.OnLostFocus = "[Event Procedure]"
           Case "RetireDate"
                  T.txt1.OnGotFocus = "[Event Procedure]"
                  T.txt1.OnLostFocus = "[Event Procedure]"
       End Select
       C.Add T 'add to collection Object
   End If
Next

'Scan for TextBoxes on the Sub-Form
For Each ctl In frm.frmSubForm2.Form.Controls
   If TypeName(ctl) = "TextBox" Then
   
      Set T = New ClsSubForm
      Set T.txt1 = ctl
      Set T.sFrm = frm
      
       Select Case ctl.Name
           Case "EMail"
               T.txt1.AfterUpdate = "[Event Procedure]"
           Case "Mobile"
               T.txt1.OnLostFocus = "[Event Procedure]"
       End Select
       C.Add T 'add to collection Object
   End If
Next

End Sub

The change is in the For Each . . . Next statement where we sets the reference to the Sub-Form Controls to pick the Text Box Control from there and add it to the Collection Object, after enabling the required built-in Events.

On the first sub-form the For. . . Next Loop uses the following reference:

For Each ctl In frm.frmSubForm1.Form.Controls
.
.
.
Next

The ctl control carries the reference (address) of the sub-form Text Box while it is added to the Class Module ClsSubForm Txt1 Property and goes to the Collection Object as it's Item.

Same way the second Sub-form Text Box references are used in the For . . .Next Loop.

For Each ctl In frm.frmSubForm2.Form.Controls
.
.
.
Next

Both the sub-Forms are set with their Has Module Property Value to Yes. Both sub-Form Class Module does not have any VBA Code.

The Main Form (frmMain) Module Code is given below:

Option Compare Database
Option Explicit

Private T As ClsSubFormHeader

Private Sub Form_Load()
  Set T = New ClsSubFormHeader
  Set T.mFrm = Me
End Sub

The ClsSubFormHeader derived Class is declared as Object T.  On the Form_Load() Event the  ClsSubFormHeader Class Object is instantiated.  The current Form Object is passed to the Property Procedure T.mFrm. of ClsSubFormHeader

If you check the ClsSubFormHeader Class Object Code you can see that this Form Object is passed to the frm2 Property of ClsSubForm, through the Property Set Procedure sFrm, in the statement Set T.sFrm = frm along with each instance of the Class Module ClsSubForm, for each Text Box in Collection Object Item.

Summary

The Sub-Form Control references are added to the Class Module Instance Property like any other control on the Main Form.  This is required when the enabled built-in Event triggers it sync with the correct Class Object instance and executes the Event Procedure.

You may download a Demo Database with all the Forms and VBA Code to try out and study the Code.

You may leave your observations, suggestions, comments on the Comment Section of this Page.


  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
Share:

Access Form Control Arrays and Event-3

Introduction

This is the continuation of last Week’s Topic on Text Box AfterUpdate and LostFocus Event capturing and performing Validation Checks on values, in Class Module Array.

Last week we have stopped short of discussing how to move all the VBA Code from the Form_Load() Event Procedure into another Class Module and leave the Form Module almost empty of all Event Procedures.  The VBA Code that defines the Text Box Control Class Module Array and invoking the required built-in Events in their respective Array elements.  This method will need only three or four lines of Code on the Form Module and will take care of all the actions in a Derived Class Module Object.

Earlier we have created Derived Class Objects using Class Module as Base Class to extend the functionality of the Base Class.  We will do that here too.

We have taken the Text Box Control first, instead of any other Control on the Form, for the Array based examples because Text Boxes are the main controls used on Form.  Text Box have several Events, like BeforeUpdate, AfterUpdate, OnLostFocus, OnEnter, OnExit, KeyDown, KeyUp, OnKey and several others, and depending on the requirements we can invoke one or more of the above Events in the Derived Class Object. 

We can write some standard Event Procedures in the Class Module for all of the above sample Events  in the Text Box’s Class Module.  But, invoke only those required one with the statement  obj.txt.EventName = “[Event Procedure]” during the Array element declaration time. 

The Class Module Event Sub-Routines may require customization of Code for different Form’s Text Box control values.  The best approach is to create a Text Box Class Module Template with most frequently used Event Procedures.  Create a copy and customize it for specific requirement.

Other Controls on the Form, like Command Buttons, Combo Boxes, List Boxes and others, mostly  use the Click or Double-Click Events only. We will take up this topic of managing different type of control Arrays on the same Form.

We will explore the possibility of better ways than Arrays to manage instances of different type of  Controls on the Form.

Moving Form's Class Module Code to Derived Class Module

Coming back to today’s topic, moving the Form Module Code to a separate Class Module, we will create a Derived Class Module Object using the ClsTxtArray1_2 Class Module as Base Class.  Move the Form Module Form_Load() Event Procedure Code into the new Class Object.

If you have not downloaded last week’s Demo Database then download it from the following link, before continuing.  We will make copies of the Modules and Form to modify the Code so that you will have both version of the Code and Form on the same database.  After making changes to the Code and Form Module you can instantly run and see how it works.

After downloading the database Open it in Ms-Access.  You may open the Form Module and check the Code.

We have to copy the Class Module (ClsTxtArray1_2) Code into a new Class Module with a different name ClsTxtArray1_3, but without any change in the code.  Form also must  be copied with a different name frmTxtArray1_3Header.  The changes, if any, will be done in the new Copy leaving the original safe and un-altered.  This way the earlier Form and Class module will remain safe.

We will use last week’s sample Form (the image is given below) and the Form Module VBA Code also reproduced below for reference.

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_2

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     If ctl.Name = "Text8" Then
       Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Else
       Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End If
     
  End If
Next
End Sub

Make a Copy of the above Form and name it as frmTxtArray1_3Header.

Create a new Class Module with the name ClsTxtArray1_3.  Copy the VBA Code from ClsTxtArray1_2 Class Module and Paste it into the new Module.

Last week’s Class Module ClsTxtArray1_2  Code reproduced below for reference.

Option Compare Database
Option Explicit

Private WithEvents Txt As Access.TextBox

Public Property Get mTxt() As Access.TextBox
  Set mTxt = Txt
End Property

Public Property Set mTxt(ByRef txtNewValue As Access.TextBox)
  Set Txt = txtNewValue
End Property

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        'Valid value range 1 to 5 only
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        'validates in LostFocus Event
    Case "Text10"
        'valid value 10 characters or less
        'Removes extra characters, if entered
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        'Date must be <= today
        'Future date will be replaced with Today's date
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal & vbCr & "Corrected to Today's Date."
          Txt.Value = Date
        End If
    Case "Text14"
        'A 10 digit number only valid
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot be left Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The Derived Class: ClsTxtArray1_3Header

The ClsTxtArray1_3 Class Module will be used as the Base Class for our new Derived Class Module, we will name it as ClsTxtArray1_3Header, with extended functionality.

Create a new Class Module with the name ClsTxtArray1_3Header. The Derived Class Module, with it's Properties and Property Procedures are given below:

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_3
Private frm As Access.Form

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
 'Form Module Code goes here
End Sub

Copy and paste the above Code into the new Header Class Module you have created.

Check the first two Property declarations.  First Property ClsTxtArray1_3 Class Object is instantiated as an undefined Array: Ta() – Ta stands for TextBox-Array.

Next Property frm is to take control of the Form, from where we plan to transfer the VBA Code here and whatever we did there will be done here. 

Next Get/Set Property Procedure will take care of the Form’s references.  It is  Set Property Procedure not Let, because we are passing a Form Object to it.

Immediately after the Form’s reference is received in the Set Property Procedure we call the Class_Init() (this is not the same as Class_Initialize() that runs automatically when a Class Object is instantiated) sub-routine to run the same code moved here from the Form’s Module.

Now, we will transfer the following Code from the Form_Load() Event Procedure into the Class_Init() sub-routine and make changes in the Form Module.

Copy and Paste the following lines of Code from the Form Module into the Class_init() sub-routine, replacing the Comment line:

Dim cnt As Integer
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End Select
     
  End If
Next

Form's Class Module Code

Open the Form frmTxtArray1_3Header in design view. Display the Code Module. Copy and Paste the following Code into the Form's Module, overwriting the existing Code:

Option Compare Database
Option Explicit

Private T As New ClsTxtArray1_3Header

Private Sub Form_Load()
  Set T.mFrm = Me
End Sub

We have instantiated the Derived Class ClsTxtArray1_3Header in Object Variable T,. With the statement Set T.mFrm = Me the active form's reference is passed to the T.mFrm() Set Property Procedure.

Immediately after this action, on the form_Load() Event procedure, the Class_Init() sub-routine runs in the ClsTxtArray1_3Header Class and the txtArray1_3 Class Object array elements are created with invoking Events for each Text Box on the Form.  Hope you are clear with the Code above.

If you are ready with modifying the Form Module, Compile the database to ensure that everything is in order.

Save and close the Form, Open it in Normal View and try out each Text Boxes and ensure that their Event sub-routines are performing as expected.

Replacing Class Object Array with Collection Object Items

The TextBox Class Object Array method works fine for several Text Boxes.  But, to create an Array, it should have a counter variable, Re-dimension the array for new element preserving the data in earlier array elements.  Increment the counter variable for the next Text Box on the Form and so on. 

When there are several controls of the other types also on the Form ( like Command Buttons, Commbo Boxes etc.)  we need to create Arrays of that Type’s Class Objects separately, with separate counter and re-dimension those controls Array too in the Class Module.  We will try these sometime later to learn how to do it.

But, a better way to manage these complex situation is to use the Collection Object, in place of Array.  We will run a demo here itself with the Text Boxes to get a feel of this method.

  1. Create a new Derived Class Module with the name ClsTxtArray1_3Coll.
  2. Copy and Paste the following Code into the Class Module:
Option Compare Database
Option Explicit

Private C As New Collection
Private Ta As ClsTxtArray1_3
Private frm As Access.Form

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
'-----------------------------
'Usage of Collection Object, replacing Arrays
'-----------------------------
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     
     Set Ta = New ClsTxtArray1_3  'instantiate TextBox Class
     Set Ta.Txt = ctl 'pass control to Public Class Property
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta.Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta.Txt.AfterUpdate = "[Event Procedure]"
     End Select
     C.Add Ta 'add to Collection Object
  End If
Next

End Sub

A Collection Object Property is declared and instantiated at the beginning. 

The TextBox Class Module is defined, not instantiated, in Object Variable Ta.

The TextBox Class Ta Object is instantiated within the Control Type Test condition.  A new Ta Object instance is created for each TextBox on the Form.

After enabling the Events the Ta Class Object is added to the Collection Object as it’s Item.

This method is repeated by adding new instance of the TextBox Class Object for  each Text Box on the Form, with it’s required Events enabled, as a new Item to the Collection Object.  The Code is cleaner than the Array method.

Make a Copy of the Form frmTxtArray1_3Header with the name frmTxtArray1_3Coll

  1. Open it in Design View and display the Form's Code Module.
  2. Copy and Paste the Following Code into the Form Module, replacing the existing Code.
Option Compare Database
Option Explicit

Private Ta As New ClsTxtArray1_3Coll

Private Sub Form_Load()
  Set Ta.mFrm = Me
End Sub

The only change here is the Derived Object's name change to ClstxtArray1_3Coll. Compile the database again.

Save the Form, Open it in normal View. Test the Text Boxes as before.

It should work as before.

You may download the database with all the Modules and Forms with all the suggested changes.


  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet Class Module MS-Access Scurity MS-Access and Internet Queries msaccess reports Array External Links msaccess tips Accesstips Menus and Toolbars Objects Downloads MsaccessLinks Process Controls Property Art Work Event Collection Object Controls WithEvents msaccess How Tos Graph Charts VBA msaccessQuery Combo Boxes List Boxes Command Buttons Data Emails and Alerts Form Query Report Calculation Command Button Custom Wizards DOS Commands Data Type Dictionary Object ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference RaiseEvent msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files reference restore switch toolbar updating upload

Featured Post

WithEvents and Report Line Highlighting

Introduction This is really a re-run of an earlier Post: Highlighting Reports published during August, 2007.  The full Code was written on ...

Labels

Blog Archive

Recent Posts