How To Find Next Autonumber Value

[Wayne]

I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActual s" table:

SELECT CDUGActuals.CDU GActID, CDUGActuals.Act ualTonnes, [CDUGActID]+1
AS NextID
FROM CDUGActuals;

This works well as long as no autonumber values have been skipped due
to deletions or cancelled entries. Is there a simple change I can
make so that the "NextID" field will show the next highest autonumber
value irrespective of if autonumber values have been skipped?

I thought of using a dedicated numbering field and populating it
sequentially using the Dmax function, but realized that this would not
be suitable because I may need to apply this query to a filtered list
from the table in which case both the autonumber field and the
dedicated numbering field would not be sequential.

[Salad]

Re: How To Find Next Autonumber Value

Wayne wrote:

I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActual s" table:
>
SELECT CDUGActuals.CDU GActID, CDUGActuals.Act ualTonnes, [CDUGActID]+1
AS NextID
FROM CDUGActuals;
>
This works well as long as no autonumber values have been skipped due
to deletions or cancelled entries. Is there a simple change I can
make so that the "NextID" field will show the next highest autonumber
value irrespective of if autonumber values have been skipped?

Not that I know of.

Let's say I have a table Customers with CustID (autonum) and CustName.
What's CustID = 4? Who's 4? I'm interested in the name. CustID is the
field I use to reference a record when linked to other tables.

If I needed something of your needs I'd consider creating another field.
Like NextCustID. Make it a Long, unique value. No stuff it with a
number that moves sequentially and use that as your reference number.

Or in a query you could get the count of autonums less/equal to your ID
+ 1. Ex:
NextID : Dcount("*","Tab leName","ID <= " & [ID]) + 1

The other option is to remove the Autonumber field and make it a Long
and create a routine to save your sequential number.

Turn Around

http://www.youtube.com/watch?v=JYB_hvVBFYc

>
I thought of using a dedicated numbering field and populating it
sequentially using the Dmax function, but realized that this would not
be suitable because I may need to apply this query to a filtered list
from the table in which case both the autonumber field and the
dedicated numbering field would not be sequential.

[Tony Toews [MVP]]

Re: How To Find Next Autonumber Value

Wayne <cqdigital@volc anomail.comwrot e:

>I'm using the following SQL statement to find the next highest
>autonumber value in a table where "CDUGActID is the autonumber field
>in the "CDUGActual s" table:
>
>SELECT CDUGActuals.CDU GActID, CDUGActuals.Act ualTonnes, [CDUGActID]+1
>AS NextID
>FROM CDUGActuals;

Why are you doing this? What is your objective?

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

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Wayne]

Re: How To Find Next Autonumber Value

Why are you doing this?  What is your objective?
>
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 athttp://www.granite.ab. ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Thanks for the reply. The objective was to grab the previous record in
a query. After lot of trawling the newsgroup I'm now persuing another
method.

[Fred Zuckerman]

Re: How To Find Next Autonumber Value

"Wayne" <cqdigital@volc anomail.comwrot e in message
news:e11efebb-ba9e-48ff-bd3d-25bb001a3fe2@i2 9g2000prf.googl egroups.com...

>Why are you doing this? What is your objective?
>Tony
>Tony Toews, Microsoft Access MVP

>Thanks for the reply. The objective was to grab the previous record in
>a query. After lot of trawling the newsgroup I'm now persuing another
>method.

While trying to determine the next autonumber is usually not a worthwhile
pursuit. It does raise the question why it can't be easily done. Access must
keep that number 'somewhere' internally..... right?
Fred Zuckerman

[Tony Toews [MVP]]

Re: How To Find Next Autonumber Value

Wayne <cqdigital@volc anomail.comwrot e:

>Why are you doing this?  What is your objective?

>
>Thanks for the reply. The objective was to grab the previous record in
>a query. After lot of trawling the newsgroup I'm now persuing another
>method.

Yes, I realize that is your immediate objective. But why are you doing this? What
business logic are you trying to perform?

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

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[David W. Fenton]

Re: How To Find Next Autonumber Value

"Fred Zuckerman" <ZuckermanF@sbc global.netwrote in
news:p_rsj.1088 1$J41.6328@news svr14.news.prod igy.net:

While trying to determine the next autonumber is usually not a
worthwhile pursuit. It does raise the question why it can't be
easily done. Access must keep that number 'somewhere'
internally..... right?

Surely there's a way to get at the seed value (though it may be
available only through ADO and not DAO), but I don't know how it's
done.

But I also don't know *why* you'd ever want to know, since if you're
using Autonumbers, the values shouldn't be meaningful in the first
place. It's only if you're maintaining your own sequence that the
numbers can have meaning, and in that case, you should have no
trouble figuring out what the next one should be.

Dunno if it helps, but you can always do a non-equi join with two
copies of the table, having the join link on Autonumber =
Autonumber-1. Or you can do the join with a WHERE clause.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/