Set Autonumber starting number?

[Maine]

I'm creating a database with a field that needs to increment by one each time a new record is added. I assume I should use the autonumber. The beginning number, however, is not "1" so how do I tell that field to start at my number? Really appreciate all the help.

 

[ssecca]

If the table is going to exist always, that is it is not going to be create on the fly an easy way is to create teh table then create an exact dublicate except change the data type of the dublicate tables autonum field to number long. Open the duplicate table and enter a fake set of data with the what will be the autonum field with the nuber before what you want to start with. close the table and create a query and append the dublicate table into the reak table al columns including the autonum column. Now open the real table delete the fake data the next entry will start with your desired number.

ssecca

 

[KornGeek]

If you require it to always increment by 1, and have no gaps in the data, then autonumber is not the choice for you. The general rule is that if the field is to have any significance whatsoever, other than simply being a record identifier, then autonumber is not the best choice.

When a user goes to create a record with autonumber, the next number in the sequence is chosen. However, if the record is cancelled before it is created, the autonumber doesn't add the value back into the sequence.

For instance, if you have records 1, 2, and 3, and then you go to add record 4, but cancel before adding it. The next record you add will be 5. You will now have 1, 2, 3, and 5. Such is the nature of autonumber.

An alternative would be to use something such as
DMax("[FieldName]", "tablename&quot + 1
to find the next highest value.

However, if you're set on using autonumber, this link may help you do what you want. Use it at your own risk. I haven't tried it, or even actually read much of it, so I'm unable to comment on how well it works (or doesn't).

Good luck. Sleep is for people with no caffeine.

 

[Maine]

KornGeek
I want to try your suggestion but I can figure out how exactly to do it. I tried adding a field to my form and putting the DMax into the several places but it doesn't work. Can you give me further instructions? Thanks very much.

 

[KornGeek]

The way I have done this is to use the BeforeInsert event on a form linked to the table. I use a line of code along the lines of

[Forms]![frmMyForm]![txtIDNumber] = DMax("[IDField]", "tblMyTable&quot + 1

Hope that helps.

 

[ajdesalvo]

Just as a FYI;

You can force an AutoNumber field to start with a specific number by appending a single record with the number you wish to start with th autonumber field with.

Then when you enter a new record it will use that number to increment from.

Then you can delete the record you used to start the autonumber with. Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"