Help and Support
 

powered byLive Search

ACC: How to Display a Line After Specific Records on a Report

Retired KB ArticleThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
View products that this article applies to.
Article ID:155329
Last Review:January 19, 2007
Revision:2.1
This article was previously published under Q155329
Moderate: Requires basic macro, coding, and interoperability skills.

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.

Back to the top

MORE INFORMATION

The following example creates a report based on the Sales By Category query in the sample database Northwind.mdb (or NWIND.MDB in version 2.0). 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.

NOTE: In version 2.0, each time they appear in the following steps, type a space in the following field names: CategoryName, ProductName, and ProductSales.
1.Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
2.Create a new, blank report based on the Sales By Category query.
3.On the View menu, click Sorting And Grouping.
4.In the first Field/Expression row, click CategoryName and set the following properties:
Sort Order: Ascending Group Header: Yes Keep Together: Whole Group
5.In the second Field/Expression row, click ProductSales and set the following property:
Sort Order: Ascending.
6.Close the Sorting And Grouping dialog box.
7.If the field list is not displayed, on the View menu, click Field List.
8.Drag the CategoryName field from the field list to the CategoryName Header section of the report.
9.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.
10.Add a text box control to the CategoryName Header section and set the following properties:
Name: Total ControlSource: =Sum([ProductSales])
11.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
12.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
13.On the View menu, click Code. Add the following line to the Declarations section of the report's code module:
Dim SalesPct as Single
14.Close the code module.
15.Set the OnFormat property of the CategoryName Header section to the following event procedure:

In Microsoft Access 7.0 and 97
------------------------------ 
       Private Sub GroupHeader0_Format(Cancel As Integer, _
          FormatCount As Integer)
       SalesPct = .1
       End Sub

In Microsoft Access 2.0
----------------------- 
       Sub GroupHeader3_Format (Cancel As Integer, FormatCount As Integer)
       SalesPct = .1
       End Sub
16.Set the OnFormat property of the detail section to the following event procedure:

In Microsoft Access 7.0 and 97
------------------------------ 
       Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
       If Me![Pcnt] >= SalesPct Then
          Me![RedLine].Visible = True
          Select Case SalesPct
             Case .1
                SalesPct = .4
             Case .4
                SalesPct = 999
          End Select
       Else
          Me![RedLine].Visible = False
       End If
       End Sub

In Microsoft Access 2.0
----------------------- 
       Sub Detail1_Format (Cancel As Integer, FormatCount As Integer)
       If Me![Pcnt] >= SalesPct Then
          Me![RedLine].Visible = True
          Select Case SalesPct
             Case .1
                SalesPct = .4
             Case .4
                SalesPct = 999
          End Select
       Else
          Me![RedLine].Visible = False
       End If
       End Sub
17.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%.

Back to the top

REFERENCES

For more information about sorting and grouping, search the Help Index for "sorting" or "grouping" and view the Help topics provided, or ask the Microsoft Access 97 Office Assistant.

Back to the top

APPLIES TO
•Microsoft Access 2.0 Standard Edition
•Microsoft Access 95 Standard Edition
•Microsoft Access 97 Standard Edition

Back to the top

Keywords: 
kbhowto KB155329

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by E-mail, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.