WithEvents and Report Line Highlighting ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

This article is essentially a revisit of an earlier post titled “Highlighting Reports” (August 2007). In that earlier version, the entire code resided within the Report’s Class Module.

The key change here is that we are moving all the code from the Report’s Class Module into a standalone Class Module, thereby freeing up the Report’s own module. The Print event of the Report’s Detail section is now captured within the Class Module object, which executes the code to highlight the desired report line item.

If you have experimented with the sample reports included in the demo databases from the previous two posts. You will find that much of the code used here (in the Class Module) is already familiar to you.

If you have not yet seen those articles, their links are provided below:

WithEvents and Access Report Event Sink.
WithEvents and Report Lines Hiding.

The Highlight of this Project.

The OnPrint() event procedure of the Report’s Detail section draws an oval-shaped outline around the Text Box that displays the exam marks retrieved from the Students table.

If a student fails to achieve the minimum passing mark of 60%, the corresponding marks are visually highlighted by enclosing them within this oval-shaped outline.

A sample image from the Report’s Print Preview demonstrating this feature is shown below:

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

Even though the report design is quite simple, I would like to draw your attention specifically to the Text Box that displays the marks, where we dynamically draw the oval shape to highlight the values.

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

The oval shape is drawn within the boundaries of the Total Text Box. However, the size of this Text Box should be chosen carefully—it should neither be too wide (like the Remarks label) nor too narrow. Although the shape can still be drawn within any-sized Text Box, if the size is inappropriate, parts of the oval may overlap the text or fail to align properly around the marks.

The Class Module.

More details on this when we review 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’s property declarations, the first line defines the Report object in the Rpt variable.

The next two lines declare the Detail and Footer sections of the Report as secRpt and secFutr objects, respectively.

Following that, three TextBox objects—txt, max, and pct—are declared to access values from their corresponding controls on the Report.

Finally, a Label control is declared to function as a legend symbol. This label, together with another label captioned “Not Successful,” will serve as a visual indicator explaining the meaning of the oval symbol drawn around a student’s marks.

The Property Get procedure is not actually required in this module and is included only for completeness, as the Rpt object is never accessed from outside the module.

The Property Set procedure receives the current Report object as a parameter from the Report’s Open (or Load) event procedure and assigns it to the Rpt object.

After this assignment, the Detail and Footer sections of the report are assigned to the secRpt and secFutr properties, respectively. These assignments also enable their Print events to be captured when they are triggered on the report.

Finally, the next three lines in the code assign the report’s TextBox controls to the txt, max, and pct properties declared in the module’s global area.

There is an empty Label control named Legend placed in the Report Footer section. A Label property named lbl is declared in the global area of the Class Module. Within the Set property procedure, the Legend label is assigned to the lbl property using the statement:

Set lbl = Rpt.Controls("Legend").

The Class Module contains three subroutines:

secRpt_Print() — triggered by the Print event of the Detail section.
secFutr_Print() — triggered by the Print event of the Report Footer section.
DrawCircle() — a shared routine called from both of the above subroutines to draw an oval (ellipse) shape around certain Total text boxes in the Detail section, and around the Legend label in the Footer section.

The Report Detail Section Print Event

When the Report’s Detail section begins printing (in Print Preview mode, not in Report View), the Print event is triggered, and the secRpt_Print() subroutine captures this event and starts executing the code.

The values from the TextBox properties (max, pct, and txt) are read into the local variables m_max, pp, and curval, respectively. The student’s percentage of marks is then calculated with two decimal precision and compared against the pass percentage (pp). Based on this comparison, the result—Passed (TRUE) or Not Successful (FALSE)—is stored in the Boolean variable yn.

Finally, the DrawCircle() subroutine is called, passing yn as the first parameter and the Total TextBox control as the second parameter.

The DrawCircle() Sub-Routine.

The DrawCircle() subroutine first checks whether the Boolean value received as its first parameter is TRUE or FALSE. Based on this, a local Boolean variable named bolPrintCircle is set accordingly. This variable acts as a flag to signal whether the circle-drawing code segment should execute or be skipped.

In this sample demo, the focus is on highlighting the marks of failed students. When a student’s calculated percentage is below 60%, the yn flag is set to FALSE. Consequently, when yn is FALSE, bolPrintCircle is set to TRUE, instructing the routine to draw an oval shape around those marks.

The TextBox’s positional values—Left, Top, and its Width and Height—are then used to calculate the center point (horizontal and vertical coordinates) of the circle. The radius of the circle is determined as half the width of the TextBox.

If the TextBox is too wide, the circle drawn within it will appear distorted—the top and bottom parts may be cut off, while the left and right edges will look like two separate arcs. To fix this, you need to reduce the vertical radius of the circle relative to the horizontal radius calculated from the TextBox width.

This adjustment is done by setting the aspect ratio of the circle. For example, by setting

sngAspect = 0.25, the vertical radius becomes one-fourth of the horizontal radius, producing a neat oval shape around the TextBox value instead of a distorted circle.

Aligning Text inside the Text Box

The TextBox value is horizontally centered within the control. However, vertically, the text usually appears near the top edge of the TextBox (and therefore close to the top edge of the circle as well). To visually center the value vertically inside the oval shape, the Top Margin is manually set to 0.1 cm in Design View. This property can only be adjusted at design time, not through code.

In the Report Footer Section, there is a label control named Legend. During the Report_Footer_Print() event, the DrawCircle() subroutine is called with this label control as a parameter to draw an oval shape inside it. Another label control with the caption “Not Successful” is placed alongside, serving as a legend to explain the meaning of the oval shape drawn around the marks of students who did not achieve passing scores.

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 try out the Report and Code.

Download MarksHighlight2007.zip

Download MarksHighligh2003.zip

LEARN MS-ACCESS TIPS AND TRICKS