Creating a Sequential Record Number Field in a Query

[James]

Hello there,

Does anyone know how to create a sequential
record number field in a query??
Thanks,

James

[Larry Linson]

It can be done by including a calculated field based on a Function, but you
have to be very careful, because I've found that (1) you must pass it a
field or every record will be "1" as it will run only once, and (2) that
field must be the unique id, and you must check that you run the function
only once per unique id, and not for any unique id's lower* than the highest
you've done already, because it may well be called more than once for each
record (don't ask me why).

* if sorted in ASC, higher if sorted DESC

Larry Linson
Microsoft Access MVP

"James" <ja*********@ntlworld.com> wrote in message
news:TfUic.171$cy3.74@newsfe1-win...

Hello there,

Does anyone know how to create a sequential
record number field in a query??
Thanks,

James

[Stephen Lebans]

See:
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.

Here's an update to the Serialize function by Peter Schroeder:

Good to hear. FWIW, here's the version I came up with today, based off
of your code and Ken's(Getz) suggestion, with a few changes:
Function Serialize(qryname As String, keyname As String, keyvalue) As
Long

Dim rs As Recordset
On Error GoTo Err_Serialize

Set rs = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)

rs.FindFirst Application.BuildCriteria(keyname, rs.Fields(keyname).Type,
keyvalue)

Serialize = Nz(rs.AbsolutePosition, -1) + 1
Err_Serialize:

rs.Close

Set rs = Nothing

End Function
Peter Schroeder

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"James" <ja*********@ntlworld.com> wrote in message
news:TfUic.171$cy3.74@newsfe1-win...

Hello there,

Does anyone know how to create a sequential
record number field in a query??
Thanks,

James

[James]

Hi Stephen,
wow, that's brilliant!

I can cope with this, however, is there an easier way of doing it?
James
"Stephen Lebans" <Fo****************************************@linval id.com>
wrote in message news:iQ*********************@ursa-nb00s0.nbnet.nb.ca...

See:
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.

Here's an update to the Serialize function by Peter Schroeder:

Good to hear. FWIW, here's the version I came up with today, based off
of your code and Ken's(Getz) suggestion, with a few changes:
Function Serialize(qryname As String, keyname As String, keyvalue) As
Long

Dim rs As Recordset
On Error GoTo Err_Serialize

Set rs = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)

rs.FindFirst Application.BuildCriteria(keyname, rs.Fields(keyname).Type,
keyvalue)

Serialize = Nz(rs.AbsolutePosition, -1) + 1
Err_Serialize:

rs.Close

Set rs = Nothing

End Function
Peter Schroeder

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"James" <ja*********@ntlworld.com> wrote in message
news:TfUic.171$cy3.74@newsfe1-win...

Hello there,

Does anyone know how to create a sequential
record number field in a query??
Thanks,

James

[Salad]

James wrote:

Hi Stephen,
wow, that's brilliant!

I can cope with this, however, is there an easier way of doing it?
James

That's one of the funniest replies I've read in this newsgroup.