Unofficial Access Stuff

Use your pdf converter to make your pdf files easy! You can now buy software that makes converting pdf to doc possible! Did you know you can even convert pdf to word?

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Host your Web site with PureHost!

Sunday, September 17, 2017 – Permalink –

Auto Number

Don't be smart

There should not be any "intelligence" in an AutoNumber field. It is meant as an index field and not anything else.

If the need should arise to reset the field, if your table does NOT contain any records, simply compacting the database again will set the Autonumber field back to 1.

Another way would be to delete the AutoNumber field and re-insert it in the table.

Here's a long way to start at a specific number.

Create your table with an AutoNumber type field, but don't enter any records.

Create another table with only a single Long Integer Number type field.

This field must have same name as the AutoNumber field in the first table.

Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.

Now create an append query to append the record in the second table to the first table and run the query.

You can now delete the second table and begin entering your data into the first table.
Also: Access AutoNumber Reset

"This is some sample code that shows how to programmatically reset all AutoNumber fields in an Access Database to a correct value (whether it be 0 or the max value + 1). In addition, it contains code for Compacting and Repairing an MS Access Database. This is perfect for people who are working with a complicated Access Database and have experienced AutoNumber bugs!
And: Creating an AutoNumber field from code

See all Topics access
Labels: Customize, General, Reference, Tips, Tutorials, VBA

<Doug Klippert@ 3:15 AM

Comments: Post a Comment