Solved

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

Posted on 2014-10-28
3
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 36

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

749 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