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

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.
colinasadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slubekCommented:
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
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colinasadAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.