Solved

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

Posted on 2014-10-28
3
271 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
ID: 40409362
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 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40409449
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
ID: 40410746
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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