Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I make an autonumber start incrementing at 1000? 3
- Thread starter Tasuki
- Start date
Hi,
I once did it by pasting 1000 records from excel and then deleting it. The next record you add will be 1001. Do not "compact and repair" after deleting, or the autonumber will reset to 1.
Maybe there are easier ways.
Cheers
Sam
I once did it by pasting 1000 records from excel and then deleting it. The next record you add will be 1001. Do not "compact and repair" after deleting, or the autonumber will reset to 1.
Maybe there are easier ways.
Cheers
Sam
-
2
- #3
Tasuki,
But there's no reason to do this, and if you're planning on doing it you most likely have a misunderstanding of autonumber fields. The contents of autonumber fields should never be thought of as correlating to anything. Access will reliably generate unique autonumbers, but those numbers will not necessarily be sequential. If you start to create a new record and then cancel the action, the autonumber will be incremented. The next autonumber you get will be two higher than the last one in the database, not one higher.
Autonumbers are a great tool for identifying records, but not for communicating information (such as how many whatevers there are in the database, or even how many there ever were). If you need to communicate information such as this, you'll have to roll your own primary key. If you search here or on comp.databases.ms-access you'll find tons of ways to do this.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Remember to reward helpful tips with the stars they deserve.
But there's no reason to do this, and if you're planning on doing it you most likely have a misunderstanding of autonumber fields. The contents of autonumber fields should never be thought of as correlating to anything. Access will reliably generate unique autonumbers, but those numbers will not necessarily be sequential. If you start to create a new record and then cancel the action, the autonumber will be incremented. The next autonumber you get will be two higher than the last one in the database, not one higher.
Autonumbers are a great tool for identifying records, but not for communicating information (such as how many whatevers there are in the database, or even how many there ever were). If you need to communicate information such as this, you'll have to roll your own primary key. If you search here or on comp.databases.ms-access you'll find tons of ways to do this.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Remember to reward helpful tips with the stars they deserve.
- Thread starter
- #4
Thanks for the replies and you are absolutely correct Jeremy, but I was wondering if it was possible to start at a higher number because the way I would like my forms number is by four digits 0000. It doesn't matter if there's a break within this autonumber as long as it is unique.
I want it to be done (if possible) this way out of convenience. That way all forms will be something like SR2002-1001, SR2002-1002, SR2002-1004... rather than, SR2002- 1, SR2002- 2, ... , SR2002- 35.
This database usually doesn't go pass 500 records per year, but the database records are saved yearly, so it will never make it pass 9999 records, this is why four digits is sufficient, and makes the title of each form look consistant.
Anyway, hope there's a way for me to do this.
Thanks again,
T
I want it to be done (if possible) this way out of convenience. That way all forms will be something like SR2002-1001, SR2002-1002, SR2002-1004... rather than, SR2002- 1, SR2002- 2, ... , SR2002- 35.
This database usually doesn't go pass 500 records per year, but the database records are saved yearly, so it will never make it pass 9999 records, this is why four digits is sufficient, and makes the title of each form look consistant.
Anyway, hope there's a way for me to do this.
Thanks again,
T
-
1
- #5
Hi
If all you want is the APPEARANCE of four digits look at Format(MyAutoNum, "0000"
, but I agree with Jeremy's comments, autonumbers are a 'behind' the scenes thing, not to be viewed by users Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
If all you want is the APPEARANCE of four digits look at Format(MyAutoNum, "0000"
, but I agree with Jeremy's comments, autonumbers are a 'behind' the scenes thing, not to be viewed by users RegardsKen Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
Yeah, ken's spot on about using the format function. That will be a better solution (though as long as you compact afterwards, there's no harm done by adding 1001 records and deleting 1000).
But actually, Ken, I think it's OK to show the autonumbers to your users as long as they understand the nature of those numbers. In several of my applications the autonumber fields are visible, because it helps people (in addition to Jet) to identify the records. But I always spend some time explaining to the users that those numbers are not going to be free of gaps.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Remember to reward helpful tips with the stars they deserve.
But actually, Ken, I think it's OK to show the autonumbers to your users as long as they understand the nature of those numbers. In several of my applications the autonumber fields are visible, because it helps people (in addition to Jet) to identify the records. But I always spend some time explaining to the users that those numbers are not going to be free of gaps.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Remember to reward helpful tips with the stars they deserve.
cjonesmcse
IS-IT--Management
So if you want a user to be able to enter a record and have a number automatically assigned to it, you can't? The user HAS to enter the number manually? Microsoft's help files in Access 2002 explain how to get autonumber to start at a specific number, but I haven't been able to make it work yet. It deals with creating a temporary table with only a single field of type "number" in it. Set that field to one less than the number at which you want autonumber to begin, then create an append query that appends the new field to the autonumber field and run it. Delete the record created by the query, delete the temporary table and the query, and the next record you enter should have the number you want in your autonumber field. My problem has been that after running the query, nothing is appended. No new record has been created and autonumber still starts with 1. I'd really like to be able to have my database assign an employee ID automatically. Any ideas?
What you just described will work. There must be something wrong with your append query. Post the SQL here.
But are you sure you want to do this? What is the importance of starting with a particular number. These numbers will NOT be sequential.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
But are you sure you want to do this? What is the importance of starting with a particular number. These numbers will NOT be sequential.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Hi
Yes, the method you describe is a variation on the theme of pronate's post above.
It should work (unless you compact the database before adding the first record, in which the autonumber will revert to 1).
The real point is taht you should not be trying to impose 'logic' on an autonumber column, its only purpose is to provide a unique rowId, as explained by Jeremy Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
Yes, the method you describe is a variation on the theme of pronate's post above.
It should work (unless you compact the database before adding the first record, in which the autonumber will revert to 1).
The real point is taht you should not be trying to impose 'logic' on an autonumber column, its only purpose is to provide a unique rowId, as explained by Jeremy Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
There is a very simple way to change your autonumber.
Create a table with a key field set to autonumber. Lets call this table 1.
Now you need to create a new table. In this table, do not set autonumber in the properties. The first entry in this table requires the the key field to be 1000. Lets call this table 2.
Now just design an append query for table 2 that links the fields to table one. Run the query.
Now table one has an autonumber field that begins with 1000.
Create a table with a key field set to autonumber. Lets call this table 1.
Now you need to create a new table. In this table, do not set autonumber in the properties. The first entry in this table requires the the key field to be 1000. Lets call this table 2.
Now just design an append query for table 2 that links the fields to table one. Run the query.
Now table one has an autonumber field that begins with 1000.
If you want a serial number for the succeeding records, the best way is to create a parameter table iwth a single record in it and then read the number using VBA and increment it after.
I think autonumbers should be used for primary keys only, which means that they can be seen by users, but should be manipulated to the minimum extent possible.
This all implies that primary keys have no intrinsic significance other than identifying a particular record. For what it's worth, there are (and always will be, I guess) two camps regarding non-significant keys. My own strong conviction is that if you do use significant primary keys, you end up regretting it sooner or later and with that regret comes tedious, unnecessary work to fix the foul-up.
I think autonumbers should be used for primary keys only, which means that they can be seen by users, but should be manipulated to the minimum extent possible.
This all implies that primary keys have no intrinsic significance other than identifying a particular record. For what it's worth, there are (and always will be, I guess) two camps regarding non-significant keys. My own strong conviction is that if you do use significant primary keys, you end up regretting it sooner or later and with that regret comes tedious, unnecessary work to fix the foul-up.
- Status
Similar threads
- Locked
- Question
- Locked
- Question