Solved

How can I sequentially number "detail" rows in an Access Report.

Posted on 2014-10-28
3
259 Views
Last Modified: 2014-10-29
I am developing an Access 2007 "project" (.adp) as a front-end to data stored in a SQL Server 2005 Express database.

I have a relatively simple Access report and I would like to sequentially number the "detail" lines that get printed.

The problem is that I am not printing every record from the Report's record source. If certain records don't meet certain criteria, in the "Detail_Format" sub-procedure I make all the fields "non-visible" and the "shrink" property of these controls and the "Detail" part of the Report means that these rows do not appear.

The SQL "Record Source" query underlying the report is fairly complex and it is difficult to integerate these criteria into the T-SQL "WHERE" clause. I know how to make use of a "running sum" text box with the data property set at "=1", but that does not work when some rows are being made invisible and "shrunk".

I have also tried to make use of a global counter variable which I increment when I think I will be printing a row, and assigning that value to my text box, but even that seems to produce unreliable results. I have tried assigning it during the Detail "OnFormat", "OnPrint" and "OnPaint" events (even using the "if Format_Count = 1" sort of condition), but none seem to work as I want. It seems that even my variable gets incremented more often than it should.

Can anyone suggest a method of sequentially numbering the rows that actually get printed without being affected by the rows that are being hidden?

Many thanks.
Colin.
0
Comment
Question by:colinasad
3 Comments
 
LVL 7

Expert Comment

by:slubek
Comment Utility
Running sum is good way if you don't want to alter underlying query.
Why don't you set hidden rows counted field to "=0" instead of "=1", based on Your criteria?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
It is pretty inefficient to select rows you are not using and then have the report hide them.  Changing the recordsource is the best solution.

You will have less trouble with your own counters if you send the report directly to the printer rather than viewing it first.  When you page forward and back, the events that affect the count run multiple times.

If you go directly to print, the only event you have add extra code to is the Retreat event.  When Access formats a detail line and goes to print it, it may discover that there is no room to print it on the current page so Access has to "retreat" and then go through the page change logic to print the footer on the current page and the headers on the new page.  It then goes back and reruns the Format event.  There is a count in the format event so you can tell that this is not the first time the event has run.  You can use that to adjust your counter or you can use the Retreat event.
0
 

Author Closing Comment

by:colinasad
Comment Utility
Yes, I decided to bite the bullet and extend the "WHERE" clause in my Report's Recordsource.

Because of several choices offered to the operator in the preceding Form, I create the Report's Recordsource "on the fly" in the "Report_Open" procedure. That T-SQL command already involved "grouping" and "summing" and I was looking for an "easy" method of hiding some lines and only sequentially numbering the displayed lines

It wasn't really too much of a task to extend the "WHERE" clause; I was just being lazy, and wanted to be sure I wasn't missing some trick in the Access Report features.

Many thanks for your help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now