Forcing the value of an Autonumber field

[postmanphat]

Right then. So I've got a table (tbldata) that records Applications we are sent, with the primary key ApplicationID which is an Autonumber. When an application comes in, we give it whatever ID Access gives it when we've entered it on the system.

My boss has asked if the Application IDs can start from 1000 onwards (This is a brand new database I've built to replace a stupidly huge spreadsheet that is not being used yet - it will go live on April 1st). I set up a dummy table with 999 records in it and appended that to tbldata then deleted those 999 appended rows thinking that when I go in and put on my first proper application it will give it the ApplicationID of '1000'.

The problem is that I accidentally ran it twice and now a new record is being assigned the ApplicationID of 2000.

Hope that is clear enough. Many thanks in advance.

Dave

 

[dhookom]

Compact your database which should reset the autonumber. Then google for a better solution that doesn't involve appending 999 records.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

A SQL way:
ALTER TABLE yourTable ALTER COLUMN yourField COUNTER(1000,1);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886