Highlighting Reports ~ LEARN MS-ACCESS TIPS AND TRICKS

Report Print Event Programming.

Analyzing data stored in tables and presenting the results through reports is a key function of any Database Management System.

An Access application may contain multiple logically related tables, maintained separately to simplify data management. When it's time to generate reports, these related tables are selected and joined using common fields. The data is then filtered and formatted for output reports. Well-designed, timely reports play a crucial role in effective decision-making.

Designing reports, like form design, is an art in its own right. MS Access provides powerful report design tools that, with a little practice, you can master effectively.

Let’s consider a scenario where we are tasked with creating a report on the company’s Sales Team to review their monthly sales target achievements. Each team member is assigned a mandatory monthly sales target of $100,000. At the end of each month, management requires a performance review report. If any team member falls short of this target, their performance is closely examined to identify and address potential issues in their assigned area.

We have prepared a sample report displaying the Sales Team’s month-end performance figures. Records of those who failed to meet the Sales target Amount of $100,000 are visually highlighted with a red circle for easy identification.

The finished Report image is given below:

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

The tabular report shown above was created using the Report Wizard and is mostly straightforward in its design. However, what makes it stand out is the visual emphasis placed on values falling below the $100,000 target—each such value is marked with a red circle.

This effect is achieved with a simple VBA routine placed in the Detail_Print() event procedure. During the report’s printing or print preview, the routine evaluates each row's sales figure. If any value is less than $100,000, the code dynamically draws a red circle around that item, helping the reviewers quickly identify underperformers.

Prepare the Report.

To prepare this report, we will need two tables from the Northwind.mdb sample database. If you're unsure of the file's location, please refer to the earlier post titled "Saving Data on Forms Not in Table" for details on where to find the Northwind.mdb file.

Once located, open your current database and import the following two tables from the Northwind.mdb file:

Orders
Order Details

Click on the Query Tab in the Database Window.
Click New to create a new Query and click the Close button on the Show Table dialog control to close it without selecting a Table from the list.
Click the Toolbar button marked with SQL to display the Query's SQL Window.

Copy and paste the following SQL String into the SQL window and save it with the Query Name: SalesReportQ0.

SELECT Orders.OrderID,
 Orders.EmployeeID,
 [Order Details].UnitPrice,
 [Order Details].Quantity,
 [UnitPrice]*[Quantity] AS Extended_Price
FROM Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID;

Follow Steps 2 and 3 outlined above to create a new query based on the source data from the first query named SalesReportQ0.
1. Open the Query Design window.
2. Add the SalesReportQ0 query as the data source.
3. Switch to SQL View.
4. Copy and paste the SQL string provided below into the SQL window.
5. Name the Query as SalesReportQ and save it.
```
SELECT SalesReportQ0.EmployeeID,
 Sum(SalesReportQ0.Extended_Price) AS Total
FROM SalesReportQ0
GROUP BY SalesReportQ0.EmployeeID;
```
Click the SalesReportQ Query and select a Report from the Insert Menu. Select Auto Report: Tabular on the Report Wizard and make changes to the design to look like the sample image given below:
Right-click to open a Large Image in New Tab/Window
Step: Modify the Total Field Properties
1. Click on the Total field on your report design (in Design View).
2. Press F4 (or right-click and choose Properties) to display the Property Sheet.
3. On the Format tab of the Property Sheet, change the following values:
  - Name: Total
  - Border Color: 12632256
  Add VBA Code to Report Module
  1. Open the Report in Design View.
  2. On the Ribbon, go to View → Code
    (Or click the “View Code” button on the toolbar above.)
  3. In the Visual Basic Editor, paste the following code into the Report’s code module:
```
Option Compare Database
Dim MinTarget As Double, curVal As Double

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'  Draw ellipse around controls that meet specified criteria.
Dim ctl As Control, ctl2 As Control, intPrintCircle As Boolean
Dim sngAspect As Single, sngYOffset As Single
Dim intEllipseHeight As Integer, intEllipseWidth As Integer
Dim sngXCoord As Single, sngYCoord As Single, mNewSpeedo As Long

On Error GoTo Detail_Print_Err

MinTarget = 100000
curVal = Me![Total]
If curVal < MinTarget Then
  intPrintCircle = True
Else
  intPrintCircle = False
End If
        
    Set ctl2 = Me.Total

If Not IsNull(ctl2) Then
    Set ctl = ctl2
    
    If intPrintCircle Then
   ' change this value to adjust the oval shape of the circle.
  
    sngAspect = 0.25 'Increase/Decrease this value to adjust the shape
    
    ' Get Height and Width of Control
  
    intEllipseHeight = Me.Total.Height
    intEllipseWidth = Me.Total.Width
    
    'Calculate Center X,Y Corordinate
    sngXCoord = ctl.Left + (intEllipseWidth \ 2)
    sngYCoord = Me.Total.Top + (intEllipseHeight \ 2)
    
    ' Draw Ellipse within the Control
            Me.Circle (sngXCoord, sngYCoord), intEllipseWidth \ 2, _
            RGB(255, 0, 0), , , sngAspect
           intPrintCircle = False
        End If
    End If

Detail_Print_Exit:
Exit Sub

Detail_Print_Err:
MsgBox Err.Description, , "Detail_Print"
Resume Detail_Print_Exit
End Sub
```
  Name the report as 'Sales Report' and save it.
4. Testing and Fine-Tuning the Report
  1. Open the Report in Print Preview to see the red circles in action.
  2. The circle is drawn relative to the size of the Total textbox. If the textbox is too wide, the top and bottom parts of the ellipse might not be visible within the control boundary.
  Fixing Ellipse Display Issues
  - To ensure the circle (ellipse) fits neatly around the value:
    - Option 1: Reduce the width of the Total textbox slightly.
    - Option 2: Adjust the aspect ratio used for drawing the ellipse.
    For example, if you're using a sngAspect variable in your code like this:
```
vba
sngAspect = 0.3
```
    Try changing it to a smaller value, such as:
```
vba
sngAspect = 0.25
```
  - A smaller aspect ratio makes the ellipse taller (more circular), while a larger one makes it flatter. You can experiment with values (e.g., 0.2 to 0.4) to find the best fit for your textbox.
  Tip:
  
  After each change, open the report in Print Preview to visually verify the result.