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

Introduction

This is really a re-run of an earlier post: Highlighting Reports, published in August 2007. The full code was written in the Report Class Module. The change here is that we will free up the Report Class Module and transfer all the Codes into an independent 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 the 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.

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 a 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 TextBox 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 TextBox.

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 the Rpt variable.

The next two lines declare the Report Detail, and Footer Sections in secRpt and secFutr Object, respectively.

The next three lines declare 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 a 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 a parameter and assigns it to the Rpt Object.

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

The next three lines in the code assign the TextBox controls of the Report in txt, max, and pct TextBox 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() subroutines capture that Event and start executing the Code.

The TextBox 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() subroutine is called, passing the value in yn as the first Parameter and Total Text Box control as the second parameter.

The DrawCircle() Sub-Routine.

The DrawCircle() subroutine first checks whether the first parameter received in the boolean 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 shape or not to draw the shape.

In this sample demo, we have taken the failed students cases to highlight the Marks with an oval shape around them. 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. The half measure of the Width Value of the Text Box determines the Radius of the circle.

If the Text Box is too wide then the circle drawn in the Text Box will lose the top and bottom sides of the circle, and the left and right sides 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 one fourth of the horizontal radius or the Aspect Ratio of the circle is set as sngAspect = 0.25. The end 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 of 0.1cm manually in the 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 a 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 try out the Report and Code.

Download MarksHighlight2007.zip

Download MarksHighligh2003.zip

LEARN MS-ACCESS TIPS AND TRICKS