AutoNumber beginning with 10000

[Guest_imported]

I have an Access 97 database with job order numbers that begin with 10000 and the users want the computer to automatically generate another job order number when a new record is entered. For example it would begin with 10000 then 10001 then 10002 and so on.

 

[jebry]

Hi!

To use AutoNumber beginning at 10000 set the format to \10000.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Guest_imported]

thankyou so much, I was hoping it was something that was not too difficult.

 

[RickSpr]

Actually, setting the Format property to "\10000" does not start the Autonumber field at 10000, it only makes it display that way. If you use the field as a foreign key in another table, you'll find that the real values are still 1, 2, etc. And if you go searching for job number 10123, you won't find it, because the real number is 123.

To really start at 10000, create a copy of the table (call it Copy B). In Copy B, change the Autonumber to a Number(Long Integer), and add a dummy record, giving the Number field a value of 9999. Next, create an Append query that appends the dummy row in Copy B to the original table. Now you can delete Copy B. (At this point, you need to compact the database if the original table has ever had a record number greater than 10000 in it, since the last time it was compacted.) Finally, delete the dummy row from the original table. Important: Don't compact the database again until after you've added at least one row. Rick Sprague

 

[svanels]

Are you aware that deleting one row in the table will re-arrange all your job numbers? I suggest do not use autonumber. You have to make sure that your job number is unique. In bigger systems (sql-server, oracle etc.) they have what they call generators and triggers which take care of generating an unique number.
A "poor's man generater" can be used and it works this way:

Create a table "JobNrGen" with one numeric field, called "NextNumber".
Every time a new record is inserted, access opens JobNr, reads NextNumber, uses is it for JobNr, counts NextNumber + 1, and writes it back to JobNrGen.

This way you will be sure that your job number references will allways be the same. (handy is you have more then 1 user creating jobs).


Regards S. van Els
SAvanEls@cq-link.sr

 

[RickSpr]

Svanels, you are mistaken. Deleting a row doesn't cause any autonumbers to be changed. Once an autonumber has been assigned, it can never be changed in that record.

However, if you delete a group of the highest numbers, then compact the database, the deleted numbers will be reassigned to new rows as they are added. Perhaps that's what you were thinking of. This is only true for the highest numbers, though; if you have records 1 through 10 and you delete record 2, compacting won't cause 2 to be reassigned to a new record. Rick Sprague

 

[svanels]

Maybe MS fixed it in Access2000, but from experience I know, if I want to sabotage an access database, the first thing to look for is an auto-number S. van Els
SAvanEls@cq-link.sr