How to set running number code?

[AstonCheah]

I have a report in continuous format, how do i add a label control to run a running number in each record? example:1,2,3,4,5,6,7,8,9,10... any one can help? thanks.

 

[khawar]

creat a text box in the detail section
set its control source to =1
On the data tab of properties windows set it to running sum
Thats all

 

[AstonCheah]

Khawar,

It works, Thanks alot!!

 

[AstonCheah]

How to do this by vba code?

 

[khawar]

If the solution is so simple then why do you want to make it complicated by using vba

 

[AstonCheah]

I just wonder how the codes run and want to learn more from vba. would appreciate it you could share.

 

[khawar]

Try to make solution as simple as possible

Use code where it is needed and not where access has provided a builtin function

 

[AstonCheah]

Actually i want to learn and use it in VB 08 not access. perhaps i should post in vb08 forum. thanks alot for you advice.

 

[khawar]

vb 2008 and vba are two different things the code which is working in access will nor work in vb 2008 where you may be using crystal reports which also have built in running sum feature but for help on that you have to post in some other forum

 

[Pat Hartman]

To do this in VBA, you would create a public variable and initialize it in the Report's Open event. Then increment it in the Format event. You would need to decrement it in the Retreat event because the Retreat event backs out the currently formatted section in preparation for a page change and the Format event will be rerun after the page footer for the current page and then the page header and any group headers are printed for the next page.

 

[WayneRyan]

Khawar,

I agree, why resort to VBA when you don't have to.

Pat,

I'm not so "sure" about the retreat event. Granted that Access will format it's guts out
until something doesn't fit on the page, BUT I don't agree that it will fire the Retreat
event.

There have been a few times that I've wanted it to fire, but when it can span a page
(or maybe not), I haven't seen the event fire.

Again, I'll defer and research some more, but I don't think it behaves properly.

At any rate, Khawar is right, use the running sum.

Wayne

 

[WayneRyan]

Pat,

After minutes of exhaustive research, I don't think Access "backs up" in a
report.

If a Detail line spans a page, it doesn't fire.

If a Detail line won't fit on a page, it doesn't fire.

Does it have something to do with Grouping? Keep all on a page?

In any event, forget the VBA and use the running sum.

Wayne

 

[Pat Hartman]

The Retreat event only exists in a report. Access formats the line to be written and executes any code in the Format event for the section being printed which in the case of this post, includes incrementing a counter. If there is room on the page to print the formatted section, the Print event is called and the section is printed. If there is not enough room to print the section, the Retreat event is called. Access then runs a "page break" and then goes back to format the last detail line again. If you don't decrement the counter in the retreat event, your count is inflated because the format event can run more than once for the same detail record.

Look again. http://msdn.microsoft.com/en-us/library/aa141191(office.10).aspx

I didn't post the answer to suggest that it be used. I posted it to satisfy the intelectual curiosity of the OP. Learning VBA should be encouraged and understanding event order is critical to customizing forms and reports.