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" <a7o8e9u0@hotmail.com> wrote in message
news:4b2a7145.0401292122.7278f2e3@posting.google.c om...[color=blue]
> This is my modification on an original solution posted by Trevor Best
> (trevor@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![/color]
