Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-28
3
Medium Priority
?
289 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 39

Accepted Solution

by:
PatHartman earned 2000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

670 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