Dynamic autonumber on an MS Access Report

[irkahs]

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!

[FishVal]

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
  

  Public Function AutoNumber(ByVal varDummy As Variant, _
                             Optional ByVal lngSeed As Variant) As Long
   
      Static lngAutoNumber As Long
      If Not IsMissing(lngSeed) Then
          lngAutoNumber = lngSeed
      Else
          AutoNumber = lngAutoNumber
          lngAutoNumber = lngAutoNumber + 1
      End If
   
  End Function
   

• Create a query based on your table (lets say tblYourTable)
  

  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
  

  Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
      AutoNumber 0, 1
  End Sub
   

Good luck.

[ADezii]

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:
   

   Dim intCounter As Integer

3. In the Print() Event of your Report's Detail Section, place the following code:
   

   Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
     intCounter = intCounter + 1
     Me![ID] = intCounter
   End Sub

4. In the Print() Event of your Report's Page Footer Section, place the following code:
   

   Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
     intCounter = 0
   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.

[irkahs]

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!

[ADezii]

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!