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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Why don't you set hidden rows counted field to "=0" instead of "=1", based on Your criteria?