VBA code in report

  • Thread starter Thread starter RJG
  • Start date Start date
R

RJG

A report is based on a query. The report detail lists the line number of the
detail. I want to have a line visible under the line number at the number of
details divided by 3. For example, if 15 detail lines exist in report I want
to make the line visible under line 5, 10, and 15. If 16 detail lines, then
after 5, 11 and 16. And if 17 lines, 6, 10 and 17. I have the logic to define
the breaks (using modulo), but need some help on the VBA code within the
report detail to make the line visible. (I'm using Access 2007.)
 
You need is to add a control to the detail section. Name it
txtLineCounter. Its source should be set to =1 and running sum should be
set to overall.

TxtLineCounter MOD Count(*)\3

In the detail section's format event you need a line of code that reads

Me.SomeLineControl.Visible = TxtLineCounter MOD (Count(*)/3) = 0

Or whatever calculation your are using with the mod function.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thank you for your suggestion. Using it, I was able to get the desired
result. I found 2 minor problems to work out though.

First, I had to tie the procedure to On Print (I guess that should have been
obvious) rather than On Paint or On Format.

Second, for reasons I'm still not sure of, when I had two separate make line
visible statements if line number x or y. Only the second instance made the
line visible. When I combined the statement If line number x or y, make the
line visible, else not--it worked just fine.

Thanks again.
 
Back
Top