Draw a Circle Around Marks within a TextBox.
- WithEvents and Access Report Event Sink.
- WithEvents and Report Lines Hiding.
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:
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:
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.
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 caption: “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 places 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 is 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 students not successful. 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 circle's aspect ratio. 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.zipLinks to WithEvents ...Tutorials.
- WithEvents MS-Access Class Module Tutorial
- WithEvents and Defining Your Own Events
- withevents Button Combo List TextBox Tab
- Access Form Control Arrays and Event Capturing
- Access Form Control Arrays and Event-2
- Access Form Control Arrays and Event-3
- WithEvents in Class Module for Sub-Form TextBox Events
- WithEvents in Class Module and Data Entry
- WithEvents and Access Report Event Sink
- WithEvents and Report Lines Hiding
- WithEvents and Report Lines Highlighting
- Withevents TextBox and Command Button Arrays
- Withevents TextBox CommandButton Dictionary
- Withevents and all Form Control Types
