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



Links to WithEvents ...Tutorials.

  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
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types
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 TextBoxes 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 highlight the text, with different Colors, Font Bold Italic, or Underline.  It only needs an expression like the following in the conditional formatting dialogue box:  

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

But, we can do many other things, which are not available in conditional formattings,  like changing the font or font size and highlighting the border of the Text Box or any other styling through the Event handling in the 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 from last week's post, to prepare the 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 take place but those built-in Events are not fired on the Report View.  They are triggered only in 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.

Report options 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 TextBox controls on the Report.  After that, we can read Students Marks (txt), the Maximum Marks (max - of all Subjects), and the 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

We can't use last week's OnPrint() Event here, to hide the required Report lines, the Print Event is the final phase of other actions like the 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 take 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 code keeps repeating itself till a valid value is received in Variable i.

The next three statements assign the TextBox 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 TextBox values are read into max and pct. The curval = txt.value statement reads the students obtained Total marks and assigns them 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 a hidden state. When the Student is found in the 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, depending 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 of the Detail Section, is found to pass the Exam then the Detail Section is made visible.  That particular report line appears in the Report.  This check is done for each line of the Report and passed students only are made visible on the Report. 

Option 2: The 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.


Links to WithEvents ...Tutorials.

  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
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types

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) work well in capturing Events Raised from Form TextBoxes 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 are present on the same Form. We will definitely take them up a little later.

After a lengthy trial run with Text Box Controls on Form need some change of scene now, away from Access Forms for the time being, and go to the Microsoft Access Reports for a 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 a few Students' names and their exam 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 names, and total marks obtained.  In the Report Header area, some extra information is inserted to explain how the Pass Mark percentage of students is calculated.  The Maximum Marks for all subjects are 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 the whole number format, currently set to 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 TextBoxes MaxMarks & Percentage in 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 define 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 set 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 capture it in the secRpt_Print() sub-routine when it happens.  The Total marks of the student are saved in the 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 compare it with the pass percentage given in 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, and 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 have a complete design and set 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 the Report view mode, then right-click on an empty area in 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 are Validated and if found qualified then highlight 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 for 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 a self-test.  You may use the demo database as a reference point when you are in doubt about 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 a clue, we will hide the failed students' lines from the Report detail Section.

Downloads.




Links to WithEvents ...Tutorials.

  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
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types
Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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