Next number for an Autonumber field

Lee Cartwright

unread,

Sep 12, 2000, 8:46:09 AM9/12/00

to

Hi,

Is there anyway, within a query, to find out what the next number in an
incrementing Autonumber field will be?

Lee Cartwright

John Spencer

unread,

Sep 12, 2000, 9:08:19 AM9/12/00

to

Not that I know of.

You could get the maximum number used and still in the database. That would
work as long as you never delete records or cancel a record before input.

Jeff Boyce

unread,

Sep 12, 2000, 1:56:37 PM9/12/00

to

Sometime if you describe WHY you want it, the nice folks here in the
newsgroups have more ideas/suggestions ...

Good luck!

Jeff

Bruce M. Thompson

unread,

Sep 12, 2000, 2:05:02 PM9/12/00

to

Lee:

If it's important enough, you could always create a new record in code, save
the new autonumber value plus 1 to a variable, and then delete the new
record you just added. It will create another gap in the sequence, but will
ensure that you get the right number even if you deleted records with the
highest existing numbers (leaving a gap before the new record).
--
Bruce M. Thompson
bthmpson@big_NOSPAM_foot.com

"Lee Cartwright" <lee.car...@bull.co.uk> wrote in message
news:e2EyVdLHAHA.195@cppssbbsa04...

HSherriff

unread,

Sep 13, 2000, 9:03:01 AM9/13/00

to

Set the query "autonumber" field to sort desending. And set the query TOP
VALUES to 1. This will return the LAST autonumber. Add a calculated field to
the query something like:
NextNumber: [autonumberID] + 1

HTH
Harlan

Bruce M. Thompson

unread,

Sep 14, 2000, 3:00:00 AM9/14/00

to

Harlan:

If no records have been deleted from the table (for argument's sake) and
your last record had an autonumber value of 1000 and you deleted that last
record, the last autonumber would be 999. The next autonumber value would be
1001, not 1000 unless you compacted the file after the deletion. You cannot
depend on the highest existing autonumber value to be a value 1 less than
the next autonumber value.

--
Bruce M. Thompson
bthmpson@big_NOSPAM_foot.com

"HSherriff" <HShe...@scana.com> wrote in message
news:Oe456MYHAHA.196@cppssbbsa04...

HSherriff

unread,

Sep 14, 2000, 3:00:00 AM9/14/00

to

Bruce,
Good catch......You right (as usual).....
It wasn't till after I pressed that POST button thaat I thought about
deleted records....

Tony Toews

unread,

Sep 14, 2000, 3:00:00 AM9/14/00

to

"Lee Cartwright" <lee.car...@bull.co.uk> wrote:

>Is there anyway, within a query, to find out what the next number in an
>incrementing Autonumber field will be?

In a query, no. In code you can indeed do this. So whenever I need
to know the just added ID, for example I'm adding child records in a
related table, I use a recordset instead of an append query along with
the following code.

Frequently you have to check to see if a record in a table which
matches certain critieria exists and get its ID number. If it doesn't
exist you need to add it and then get the ID number. Use the
following code.
Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("Customers")
MyTable.AddNew
MyTable("Company Name") = "Finnegan's Foods"
MyTable.Update
MyTable.Move 0, MyTable.LastModified
CustomerID = MyTable("CustomerID")
MyTable.Close
MyDB.Close
Set MyTable = Nothing
Set MyDB = Nothing

Tony
----
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
VolStar http://www.volstar.com Manage hundreds or
thousands of volunteers for special events.