Autonumber - starting number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt, Sheffield, UK - 16 Mar 2006 17:38 GMT

How can you change which number your autonumber starts at, its also my
primary key but i want to start a number range from 7000000 then have the
database count up ?

Reply to this Message

Rick Brandt - 16 Mar 2006 18:17 GMT

> How can you change which number your autonumber starts at, its also my
> primary key but i want to start a number range from 7000000 then have
> the database count up ?

It is generally not a good idea to use an AutoNumber when you care at all
about the value (other than it being unique). However; you could insert a
record with an append query that has a value for that field of 6999999, then
delete it afterwards. All AutoNumber values would then proceed from the
next number providing that you don't compact the file before entering your
first record.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Jeff Boyce - 16 Mar 2006 19:01 GMT

Matt

In addition to Rick's comments, keep in mind that Access Autonumbers are NOT
guaranteed to be sequential. In fact, you can almost count on ending up
with apparently "missing" autonumbers in a sequence. If you are intending
to use the Autonumber value as an auditable ID, you'll probably end up
having to explain to the auditors why there are missing records...

Regards

Jeff Boyce
Microsoft Office/Access MVP

> How can you change which number your autonumber starts at, its also my
> primary key but i want to start a number range from 7000000 then have the
> database count up ?

Reply to this Message

Nouba - 16 Mar 2006 22:02 GMT

I do not doubt Rick's and Jeff's statements. I nevertheless do not want a
short solution of the problem withheld.

ALTER TABLE [Tablename] ALTER COLUMN [Autofieldname] COUNTER(NewStartnumber,
NewOffset)

Signature

Norbert Bous

Reply to this Message

Jeff Boyce - 17 Mar 2006 00:29 GMT

Norbert

I don't believe my motivation was to withhold. I was trying (perhaps
indirectly) to elicit a wider description of the perceived need. There are
undoubtedly situations in which what the original poster asked is
appropriate, but I didn't understand the situation well enough yet to offer
a final recommendation.

Knowing HOW to is not as valuable as knowing WHEN to, in my mind.

Regards

Jeff Boyce
Microsoft Office/Access MVP

>I do not doubt Rick's and Jeff's statements. I nevertheless do not want a
> short solution of the problem withheld.
>
> ALTER TABLE [Tablename] ALTER COLUMN [Autofieldname]
> COUNTER(NewStartnumber,
> NewOffset)

Reply to this Message

Ken Sheridan - 18 Mar 2006 02:08 GMT

Matt:

You'll find a demo of 'resettable' custom numbering at:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=238
39&webtag=ws-msdevapps

This uses the common technique of holding the lat number in an external
database which is opened exclusively in code, thus preventing conflicts if
two users are attempting to obtain a number simultaneously.

Ken Sheridan
Stafford, England

> How can you change which number your autonumber starts at, its also my
> primary key but i want to start a number range from 7000000 then have the
> database count up ?

Reply to this Message