Solved

Set beginning # in AutoIncrField.

Posted on 1998-09-28
6
323 Views
Last Modified: 2013-12-25
Can one set the beginning numer in a AutoIncrField?  Say, I would like to start counting at 1000.
0
Comment
Question by:RUSTY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 4

Expert Comment

by:mcix
ID: 1496470
If you are referring to Access, this article should help:

http://support.microsoft.com/support/kb/articles/q94/8/21.asp
0
 

Expert Comment

by:trillian30
ID: 1496471
If you are using ms-sql 6.5, use the seed property to set the initial value:

IDENTITY[(seed, increment)]
Generates incremental values for new rows based on the seed and increment parameters. If used, the seed value will be assigned to the first row in the table and each subsequent row will receive the next identity value, equal to the last identity plus the increment value. If neither argument is given, both will default to 1.

0
 

Author Comment

by:RUSTY
ID: 1496472
Using VB 5.0  with a jet db.  Will be creating the db in Access, however the data from mcix's comment is not available, according to Microsoft.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:trillian30
ID: 1496473
Here is some info from the Access 97 help files...
hope it helps

Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.

1      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.


2      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.

3      Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

Note   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.

4      Delete the temporary table.
5      Delete the record added by the append query.
6      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.

Note   If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

0
 

Author Comment

by:RUSTY
ID: 1496474
trillian30,

Splendid.  Please post answer so I can award points.  Thanks.

0
 

Accepted Solution

by:
trillian30 earned 10 total points
ID: 1496475
Here is some info from the Access 97 help files...
hope it helps

Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.

1 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.


2 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.

3 Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

Note   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.

4 Delete the temporary table.
5 Delete the record added by the append query.
6 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.

Note   If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Microsoft Access combo box help 2 57
Can we place a tooltip on the actual vb6 form 5 56
MS Access 03, TransferText, decimal places 8 92
2 Global Vars, 1 List Box 4 31
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question