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

Creating a Sequential Record Number Field in a Query

P: n/a
Hello there,

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

James
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


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

Nov 12 '05 #2

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


Nov 12 '05 #3

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

Nov 12 '05 #4

P: n/a
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.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.