Line numbers over entire report with multiple headers 1

[jkirkland]

I am trying to figure out how to show sequential line numbers over an entire report. If I put a running sum in the detail section then the report shows no line numbers in the headers (which the user is going to ask me why I can't make it look like their old Excel reports).

Does anyone know of a way to place sequential line numbers on a report that cross headers, detail and footers to make it look like each 'line' of the report has its own line number?

1 Header1
2 Header2
3 Detail1
4 Detail2
5 Detail3
6 Footer1
7 Footer2

Thanks!

 

[lupins46]

''the user is going to ask me why I can't make it look like their old Excel reports).'

to which the answer is - 'this isn't Excel; it's Access; and different programs have different capabilities.'

I assume there is some benefit in moving the application from Excel to Access, otherwise, why bother?

However if you have to do this then you can create a function to return a count.
In the report module put:

Public Function linenum()
Static x
x = x + 1
linenum = x
End Function

Then put a textbox on every print line in design view and set its control source to
=linenum()

 

[jkirkland]

That worked perfectly! Thank you very much. Can you explain to me how this function works? I'm relatively new to VBA.

 

[jkirkland]

Actually, it works perfectly on the initial print preview or if you print the report without previewing it first, but if you preview the report, then print it, the first line starts with 32 instead of 1.

Its not a big deal to work around. I was just curious why it does this.

Again, thanks for the help

 

[lupins46]

1 Public Function linenum()
2 Static x
3 x = x + 1
4 linenum = x
5 End Function

Referring to the line numbers-)

Line 1 says we are defining a function of our own (Function as in the built-in functions like Average, Max, min etc).
Public means that is available anywhere in this application.

Line 2 Defines a variable (a field in memory) which I've called x. Normally when yu define a variable you use Dim x.
But using Static instead of Dim means that the value of X is retained from one use of the function to the next.
(In this case it 'remembers' the previous value of x.)

Line 3 calculates the new value of X by adding 1 to the existing value (calculated last time the function was used)

Line 4 sets the value of the function to the now current value of X. This is how the value is returned to the query or whatever that called the function.

Line 5 is just an instruction to the compiler to say we've reached the end of this procedure.

So the effect of all this is that when you set the control source of the control in the report to the value of this function it simply gets the next number.

Strictly speaking, if you were running several reports in this way you would need to pass the function a flag to say that you wanted to restart from 1 again in the first line of the report, otherewise report 2 would continue from where report 1 got to.
Public Function linenum(reset as boolean)
Static x
if reset = true then x=0
x = x + 1
linenum = x
End Function

and you would call the function like this:

=linenum(True) in the first number control in a report
and
= linenum(false) in every other number control.

 

[jkirkland]

Cool.

I hadn't ever seen the 'Static' function before. I'll give the reset feature a try also.

Thanks!