ACC2000: How to Display a Line After Specific Records on a Report
ID: Q208356
|
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).
SUMMARY
This article describes how you can display a horizontal line after specific
detail records in a report, where the criteria for displaying the line
changes on a sliding scale.
MORE INFORMATION
The following example creates a report based on the Sales By Category query
in the sample database Northwind.mdb. The report groups the records by category and sorts the records in each group by total sales. It uses a variable called SalesPct to determine when a hidden line control should appear on the report. In this example, there is a red line under the first record in each group that exceeds 10% of total sales, and also under the first record that exceeds 40% of total sales.
- Open the sample database Northwind.mdb.
- Create a new, blank report based on the Sales By Category query.
- On the View menu, click Sorting And Grouping.
- In the first Field/Expression row, click CategoryName and set the following properties:
Sort Order: Ascending
Group Header: Yes
Keep Together: Whole Group
- In the second Field/Expression row, click ProductSales and set the following property:
Sort Order: Ascending.
- Close the Sorting And Grouping dialog box.
- If the field list is not displayed, on the View menu, click Field List.
- Drag the CategoryName field from the field list to the CategoryName Header section of the report.
- Drag the ProductName field and the ProductSales field from the field list to the detail section of the report. Align the two controls side by side on the same horizontal plane.
- Add a text box control to the CategoryName Header section and set the following properties:
Name: Total
ControlSource: =Sum([ProductSales])
- Add a text box control to the detail section, placing it to the right of the ProductName and ProductSales controls. Set the following properties:
Name: Pcnt
ControlSource: =[ProductSales]/[Total]
Format: Percent
DecimalPlaces: Auto
- Add a line control to the detail section. Place it directly under the Pcnt control, and make it wide enough to underline the control. Set the following properties:
Name: RedLine
Visible: No
BorderColor: 255
BorderWidth: 2 pt
- On the View menu, click Code. Add the following line to the Declarations section of the report's code module:
Dim SalesPct as Single
- Close the code module.
- Set the OnFormat property of the CategoryName Header section to the following event procedure:
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
SalesPct = 0.1
End Sub
- Set the OnFormat property of the detail section to the following event procedure:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me![Pcnt] >= SalesPct Then
Me![RedLine].Visible = True
Select Case SalesPct
Case 0.1
SalesPct = 0.4
Case 0.4
SalesPct = 999
End Select
Else
Me![RedLine].Visible = False
End If
End Sub
- Save the report as rptProductSales and open it in Print Preview. Note that in each category group, a red line appears on the report under the first detail record where the Pcnt field is greater than or equal to 10%, and then again where the Pcnt field is greater than or equal to 40%.
REFERENCES
For more information about sorting and grouping, click Microsoft Access Help on the
Help menu, type "sorting" in the Office Assistant or the Answer Wizard,
and then click Search to view the topics returned.
Additional query words:
condition multiple
Keywords : kbdta RptEvent
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo