Reset autonumber Field Programatically

  • Thread starter Thread starter Me
  • Start date Start date
M

Me

Hi everyone!



I’m having serious problems with autonumber fields.

When users delete some records on forms or tables, total number of records
is reduced but autonumber field doesn’t consider it .

Users tell me: customers table has 100 records but the ID field(Autonumber)
is 125

This is because 25 records have been deleted.

Is there any way to reset the number of autonumber field programatically
where necessary?



Best wishes
 
You can reset the Autonumber field by compacting the database. However, your
post indicates you are using Autonumbers incorrectly. Autonumbers are
arbitrary numbers assigned to records as you enter them; they have no real
value to the data. They do NO provide you with accurate record counts, nor
were they intended to do so. They are often used as surrogate key fields in
that they don't really tell you anything about the data. If you are using an
autonumber field - and ONLY that autonumber field - to guarantee uniqueness
of your data, then I would suggest that you need to rethink your design. You
can always get the count of records in a table by running a SQL query like
this:

"SELECT Count(*) AS RecCount From YourTable"
 
Why would you want to?

The autonumber is only meant to give each record a unique number. I think
you are looking for another solution. I can imagine a code that takes the
forst record and names it '1' and from there on adding '1' to each record.
This number should be recalculated I guess, but you will have to ask on of
the many codewizards around here, I am pretty much a newbe in this :-(

Good luck and start thinking about not using the autonumber for anything.
Just have it in your table and ignore it everywhere else.

Grtz Henro
 
Back
Top