Easy sequential record numbers in Access queries

[systems analyst]

This is my modification on an original solution posted by Trevor Best
(tr****@microprism.com) back in 1996.

Insert the following code in a Module in your Access data base.
Option Compare Database
Option Explicit
Dim mlngCounter As Long

Function ZeroCounter()
mlngCounter = 0
ZeroCounter = 0
End Function

Function GetCounter(pvar As Variant)
mlngCounter = mlngCounter + 1
GetCounter = mlngCounter
End Function

Insert a column in your query and insert the following code in the
Field box.
Expr1: GetCounter(any other field name in the
query)+ZeroCounter()
For example Expr1: GetCounter([tblData_Entry]![txtPayment_Year])
+
ZeroCounter()

This will start the record numbers at 1 each time you run the query.
If you want the record numbers to keep incrementing just remove the
+ZeroCounter() from the expression. As in multiple appends to a
table.

Enjoy!

[Stephen Lebans]

That solution is fine for a specific situation but if the query is used
for the recordsource behind a Form or Report, or viewed directly in a
datasheet view, then the function will fail as the expression will be
continually evaluated as the user scrolls through the recordset.
Have a look here:
http://www.lebans.com/rownumber.htm
Rownumber.zip is a database containing functions for the automatic row
numbering of Forms, SubForms and Queries.

Updated Oct. 13 by Allen Browne. Includes error handling and cleaned
code.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"systems analyst" <a7******@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...

This is my modification on an original solution posted by Trevor Best
(tr****@microprism.com) back in 1996.

Insert the following code in a Module in your Access data base.
Option Compare Database
Option Explicit
Dim mlngCounter As Long

Function ZeroCounter()
mlngCounter = 0
ZeroCounter = 0
End Function

Function GetCounter(pvar As Variant)
mlngCounter = mlngCounter + 1
GetCounter = mlngCounter
End Function

Insert a column in your query and insert the following code in the
Field box.
Expr1: GetCounter(any other field name in the
query)+ZeroCounter()
For example Expr1: GetCounter([tblData_Entry]![txtPayment_Year])
+
ZeroCounter()

This will start the record numbers at 1 each time you run the query.
If you want the record numbers to keep incrementing just remove the
+ZeroCounter() from the expression. As in multiple appends to a
table.

Enjoy!