By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Adding row numbers in an Access report

P: 7
I want to add a row number for each record in a report. I will have 10 records on each page and I want them to restart at every new page. So counting from 1 to 10 and then resetting for every new page
I have used a textbox with control source set to 1 and running sum, but the problem is to get it to reset on a new page
4 Weeks Ago #1

✓ answered by twinnyfo

Assuming your text box is named txtCount, remove the control source.

In the Report's VBA Module:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  5.     Me.txtCount = 0
  6. End Sub
  7.  
  8. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  9.     Me.txtCount = Me.txtCount + 1
  10. End Sub
I have tested this and it should work.

Share this Question
Share on Google+
12 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,135
In the Report's Page Header On Format property, set the value of the text box to 1.
4 Weeks Ago #2

P: 7
Hi,

Thank you for the answer, but I have the textbox in the "detail" section of the report to have a new row number for each record.

Page 1
1. Alan
2. Bob
3. Ceasar

Page 2
1. David
2. Eric
3. Fred

Can I use some variable that resets every page break?
4 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,135
Assuming your text box is named txtCount, remove the control source.

In the Report's VBA Module:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  5.     Me.txtCount = 0
  6. End Sub
  7.  
  8. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  9.     Me.txtCount = Me.txtCount + 1
  10. End Sub
I have tested this and it should work.
4 Weeks Ago #4

P: 7
Thank you

It looks logical but I can't get it to work.
I added the code exactly as above, removed the control source, renamed the text box to txtCount
but it doesn't show any row numbers in the report

I use Access 2013, could that be a problem?

Do I need to add something in the "expression builder" ?
4 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,135
Does your Report have a visible header? If not, it must have one, even if you set its height to 0.0104"
4 Weeks Ago #6

P: 7
It has a header, was set to 0mm height.
I set it to 0.011mm, but it still won't show
4 Weeks Ago #7

twinnyfo
Expert Mod 2.5K+
P: 3,135
Page or report header? Should be the Page header.
4 Weeks Ago #8

P: 7
I also have a visible page header with some text (headings and title)

Instead of row numbers the text "#Name?" is shown at the beginning of each row/record
4 Weeks Ago #9

twinnyfo
Expert Mod 2.5K+
P: 3,135
I have no clue what you are doing differently.


Attached Images
File Type: jpg Narvid1.jpg (29.9 KB, 169 views)
File Type: jpg Narvid2.jpg (27.3 KB, 166 views)
Attached Files
File Type: zip Narvid.zip (20.6 KB, 4 views)
3 Weeks Ago #10

P: 7
Thank you for your patience and your great solution. It is exactly what I want my report to do. I can't see any differences, but I will try to build it up from the beginning, using your example, to get it to work.

Thanks a lot
3 Weeks Ago #11

P: 7
When I rebuilded my report, I managed to get it to work. I still can't get it, why it didn't work with the original report, but anyway, now I got a working report. Thanks again Twinnyfo.
3 Weeks Ago #12

twinnyfo
Expert Mod 2.5K+
P: 3,135
It probably didn't work with the original report because Access has a mind of its own. There might be a very perfectly logical reason for its not working. But I just chalk it up to malevolent intelligence.

Glad we could hepp! Let us know if you come across any other challenges.
3 Weeks Ago #13

Post your reply

Sign in to post your reply or Sign up for a free account.