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 SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Autonum Starting Point 1
- Thread starter detwings9
- Start date
TPetersonFlorida
Programmer
the only way i know how to do something like that is to write a little function to loop 9,999 times writing dummy data to the table and then delete all of them when done. that leaves the next new record w/ 10,000 as its beginning autonumber value.
if other people know of a better way i would love to know.
if other people know of a better way i would love to know.
-
1
- #3
mario123mario
IS-IT--Management
You can also write this :
>"10000"
in the Format property of your AutoNumber field.
>"10000"
in the Format property of your AutoNumber field.
<Soapbox mode on>
It is a common mistake to try to use autonumbers to represent something outside of the database, such an a customer account number.
If the actual value of an autonumber field is important to you then you are not using the autonumber facility they way it's intended to be used.
Autonumbers are supposed to give you an easy way to generate numbers that are guaranteed to be unique. As such they are ideal for use as primary keys in your tables. These keys can then be used to link related tables together.
The only requirement for linking tables this way is that the primary keys are unique.
They do not have any other meaning, and any attempt to use them with some other meaning (such as account numbers, order numbers etc.) is only going to cause grief sooner or later.
You do not have any control over the numbering scheme used by autonumbers, beyond choosing either sequential or random incrementing.
<soapbox mode off/>
When you need a 'number' that has some meaning then you need to generate it yourself, and store it in a regular numerical field in your table. This is pretty simple to do, and you will have complete control over the format and range of the number.
The simplest thing to do is add a field to yur table, and give it an index, no-dups allowed. Then in your form's BeforeInsert event (you are using forms for data entry, yes?) you run some code to generate the number you want, with the meaning you intend it to have.
It is a common mistake to try to use autonumbers to represent something outside of the database, such an a customer account number.
If the actual value of an autonumber field is important to you then you are not using the autonumber facility they way it's intended to be used.
Autonumbers are supposed to give you an easy way to generate numbers that are guaranteed to be unique. As such they are ideal for use as primary keys in your tables. These keys can then be used to link related tables together.
The only requirement for linking tables this way is that the primary keys are unique.
They do not have any other meaning, and any attempt to use them with some other meaning (such as account numbers, order numbers etc.) is only going to cause grief sooner or later.
You do not have any control over the numbering scheme used by autonumbers, beyond choosing either sequential or random incrementing.
<soapbox mode off/>
When you need a 'number' that has some meaning then you need to generate it yourself, and store it in a regular numerical field in your table. This is pretty simple to do, and you will have complete control over the format and range of the number.
The simplest thing to do is add a field to yur table, and give it an index, no-dups allowed. Then in your form's BeforeInsert event (you are using forms for data entry, yes?) you run some code to generate the number you want, with the meaning you intend it to have.
Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.
If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.
Delete the temporary table.
Delete the record added by the append query.
If you had to disable property settings in step 3, return them to their original settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.
In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.
If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.
Delete the temporary table.
Delete the record added by the append query.
If you had to disable property settings in step 3, return them to their original settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question