Auto Numbering

[Andrea Stimson]

Hello,

I have almost completed my database, but I am having problems with the auto
numbering. It's a project database which is assigned a unique project number
each time a new project is added. On testing the database I added in some
test records which I subsequently deleted, I now want to add real project
using the next number available, but it skips out the numbers used for
records deleted which I want to use again. Is there any way I can re-use
these numbers???

Thanks for your help
Andrea

 

[OssieMac]

Rename the table containing the data.

Select the table and then copy. In the dialog box for new table name, enter
the original table name and check Structure only. This creates an empty table
with the same name and structure as the original.

Then import the data from the original table. When satisfied that all data
is imported correctly, you can delete the old table.

 

[Andrea Stimson]

Thanks for your help with this.

 

[OssieMac]

Hi Andrea,

I owe you and apology. My description was incorrect. I had exporting and
importing to Excel on my mind. Just realized when I read your reply that I
had given you the wrong info so try the foillowing.

Create the new empty table as per my original post.

Select Queries in the database window.

Double click Create query in Design View.

Select and Add the original table in the Show tables dialog and then close
dialog box.

Select all fields and drag into the query matrix.

Click the drop down arrow beside the Query type button on the toolbar.

Select Append query.

In the dialog box, click the drop down arrow in the Table name field and
select the new empty table name and click OK.

Run the query (Red Question mark button on toolbar.)

The new table should now be populated with the data from the original table.

 

[Jeff Boyce]

If you are using the Access "Autonumber" data type, you need to be aware
that it is intended for use as a unique identifier. That means it should be
unique, not sequential ... Autonumbers are generally unfit for human
consumption. If you are displaying the autonumber to humans, someone's
going to be confused (sooner or later).

Instead, consider creating a "custom autonumber" (you can search on this
term, or look at mvps.org/access for it) that WILL guarantee you have a
sequential number.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hello,

I have almost completed my database, but I am having problems with the auto
numbering. It's a project database which is assigned a unique project number
each time a new project is added. On testing the database I added in some
test records which I subsequently deleted, I now want to add real project
using the next number available, but it skips out the numbers used for
records deleted which I want to use again. Is there any way I can re-use
these numbers???

Thanks for your help
Andrea

Ossie's advice will help you salvage the existing data... BUT!!!

Autonumbers have one purpose, and one purpose only: to provide a unique
meaningless identifier for a record. They are *NOT* suitable for project
numbers or for human consumption! They will always develop gaps; not only will
a deleted record leave a permanent gap, but just hitting the <ESC> key after
starting a record will "use up" an autonumber. If gaps aren't acceptable...
simply don't use Autonumber.

Instead, use a Long Integer field and a little bit of VBA code in your data
entry form to assign a custom counter. The exact technique varies depending on
your needs - search Google Groups for "custom counter" for many threads
describing the options.

 

[OssieMac]

Hi again Andrea,

Jeff and John are absolutely right and I thought I should provide a little
more advice.

Backing up of your data before you start testing is imperative. I find that
splitting the database so the tables are in a separate database is the best
way. If you are unfamiliar with this than basically it is placing all the
tables in one database (called the backend or be) and the rest of the project
in another one (called the front end or fe). It is extremely easy to do. Look
it up in help under split.

You can then make a copy of the database with the tables to another backup
folder and then do all the testing you want as well as modifications to the
front end and then simply copy the original backend tables database back in
and you have the original unmodified data.

If during testing, you find that you want to make an alteration to a table
then copy the original table database (be) back in and modify it and then
make another backup before modifying data with testing. That way you still
maintain the original data.


Anyway, hope you find this helpful.