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

Dynamic autonumber on an MS Access Report

P: 15
Hello all,

I have a report which is connected to a query that collects data based on some filters. Now, my question is, how do I make an autonumber starting from 1 get generated everytime there is a page break for a dummy text field on the report? This field is not part of any table but just a serial number. Example below.

Page 1 of report.

ID Description Qty Price
=======================
1 x x x x x x x x x x x
2 x x x x x x x x x x x x x
3
...


Page 2 of report

ID Description Qty Price
=======================
1 x x x x x x x x x x x


As you can see the value ID does not belong to any table. Just a number which starts at 1 and displays record count.

Please help. :)


Thanks!
Sep 9 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello all,

I have a report which is connected to a query that collects data based on some filters. Now, my question is, how do I make an autonumber starting from 1 get generated everytime there is a page break for a dummy text field on the report? This field is not part of any table but just a serial number. Example below.

Page 1 of report.

ID Description Qty Price
=======================
1 x x x x x x x x x x x
2 x x x x x x x x x x x x x
3
...


Page 2 of report

ID Description Qty Price
=======================
1 x x x x x x x x x x x


As you can see the value ID does not belong to any table. Just a number which starts at 1 and displays record count.

Please help. :)


Thanks!
Hi, irkahs.

Here is one possible solution.
  • Place the following function in code module
    Expand|Select|Wrap|Line Numbers
    1. Public Function AutoNumber(ByVal varDummy As Variant, _
    2.                            Optional ByVal lngSeed As Variant) As Long
    3.  
    4.     Static lngAutoNumber As Long
    5.     If Not IsMissing(lngSeed) Then
    6.         lngAutoNumber = lngSeed
    7.     Else
    8.         AutoNumber = lngAutoNumber
    9.         lngAutoNumber = lngAutoNumber + 1
    10.     End If
    11.  
    12. End Function
    13.  
  • Create a query based on your table (lets say tblYourTable)
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblYourTable.*, AutoNumber(tblYourTable.[Any table field]) AS AutoNum FROM tblYourTable;
    [Any table field] means any table field no matter what, just to force AutoNumber function to run for each record
  • Create a report based on the query
  • In report in page header format event place the following code resetting autonumber to 1
    Expand|Select|Wrap|Line Numbers
    1. Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    2.     AutoNumber 0, 1
    3. End Sub
    4.  

Good luck.
Sep 10 '07 #2

ADezii
Expert 5K+
P: 8,616
Hello all,

I have a report which is connected to a query that collects data based on some filters. Now, my question is, how do I make an autonumber starting from 1 get generated everytime there is a page break for a dummy text field on the report? This field is not part of any table but just a serial number. Example below.

Page 1 of report.

ID Description Qty Price
=======================
1 x x x x x x x x x x x
2 x x x x x x x x x x x x x
3
...


Page 2 of report

ID Description Qty Price
=======================
1 x x x x x x x x x x x


As you can see the value ID does not belong to any table. Just a number which starts at 1 and displays record count.

Please help. :)


Thanks!
  1. Create an Unbound Text Box on you Report and Name it ID.
  2. In the General Declarations Section of your Report Module, enter the following line of code:
    Expand|Select|Wrap|Line Numbers
    1. Dim intCounter As Integer
  3. In the Print() Event of your Report's Detail Section, place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    2.   intCounter = intCounter + 1
    3.   Me![ID] = intCounter
    4. End Sub
  4. In the Print() Event of your Report's Page Footer Section, place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
    2.   intCounter = 0
    3. End Sub
  5. I do believe that this is as compact as you are going to get as far as coding is concerned - it only takes 4 lines of code to do the trick, and it is independent of the Record Source.
Sep 10 '07 #3

P: 15
Thank you guys for the much appreciated response! It works just fine. I used the second method suggested by ADezii and it works like a charm. :)

Thank you once again.


Cheers!
Sep 10 '07 #4

ADezii
Expert 5K+
P: 8,616
Thank you guys for the much appreciated response! It works just fine. I used the second method suggested by ADezii and it works like a charm. :)

Thank you once again.


Cheers!
You are quite welcome - always glad to help!
Sep 10 '07 #5

Post your reply

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