Autonumber - starting number

[Matt, Sheffield, UK]

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 ?

 

[Rick Brandt]

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.

 

[Jeff Boyce]

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

 

[Nouba via AccessMonster.com]

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)

 

[Jeff Boyce]

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

 

[Ken Sheridan]

Matt:

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

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&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