ID number

[TRC]

Is it possible to reset the ID number for the records in an ACCESS2000 database? Right now the numbers seem to be random and I would like for them to be incremental.

 

[dan-cat]

Do you mean an autonumber?

These numbers are generated sequentially, however if you delete a record the relevant autonumber will not be re-used.

 

[TRC]

Yes, an auto-number. How can these numbers be reset or formatted?

 

[dan-cat]

You really shouldn't be playing around with autonumber values.

Their purpose is to guarantee a unique id for each record in your table - nothing more. Why are you wanting to alter the values of your autonumber field?

 

[ghudson]

That is not the purpose of an autonumber field. You should not use an autonumber field if the number needs to have a specific meaning for each record in your table. You would need to create your own function to auto-increment the numbers. Search this forum for examples on how to do it. Most use the DMax function.

You can delete the autonumber field, save the table, then recreate the autonumber field if you need to "reset" the numbers.

 

[TRC]

ghudson- we are trying to delete and recreate. Our situation is this: we had an old database that we wanted to use for our current inventory so we replicated it and deleted all the old records. However, when we began to enter new records they had these exponential ID numbers (such as 2EX9) We need our records to have regular numbers (such as 109) for their record numbers because that is what we use to look up inventory.

 

[Steve56]

This has no reference whatsoever to the topic in question, but what does TRC stand for?

It just so happens to be the initials of my college and I would find it very funny if a member of staff was asking for help with databases.

 

[ghudson]

Good luck with aligning the inventory items with the numbers the old autonumber assigned. Forgetting the fact the designer of that db broke the rule for using autonumbers you will have to remove the autonumber field type for that field and manually type the numbers you want for each inventory item in that field. That is the only way to give those inventory items the number you want. The autonumber field will not do what you want for that is not it's purpose. As dan-cat stated above, the purpose of autonumbers is to guarantee a unique id for each record in your table. Search this forum for autonumber and you will see that what the others have posted on the subject.

 

[TRC]

TRC stands for Trinity Reform College. We are home to numerous ex-cons so we really do not like to advertise ourselves online.

 

[TRC]

Thanks ghudson. It really does matter to us though what the numbers are as long as they are not in the exponential format we have been getting. I think the problem may have been fixed by duplicating the old database instead of replicating it, but we have decided to quit for the day and look at it again tomorrow. Thanks again for your help. If anyone else would still like to post tonight though it is greatly appreciated!

 

[dan-cat]

Like I said - use autonumbers for whatever the hell you like