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

Easy sequential record numbers in Access queries

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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!


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.