autonumber

  • Thread starter Thread starter Bobcat
  • Start date Start date
B

Bobcat

why would the autonumber all of a suden change from a 4 digit number to an 8
digit number?

example;
9400
9401
9402
34286622
34296623
Etc

I have used "Compact and Repair Database" to no avail.
 
Maybe you have a code in an event or you had accidentally inserted a record
into your table with a 8 digit number. The autonumber will auto increment
with this new number.
 
I have delete all 8 digit records and then did another compact and repair to
no avail.

the database is secured and I am the only one with admin access.

should i try to delete the last 4 digit number? 9402 then compact and repair?
 
Bobcat said:
I have delete all 8 digit records and then did another compact and
repair to no avail.

the database is secured and I am the only one with admin access.

should i try to delete the last 4 digit number? 9402 then compact and
repair?
Click to expand...

In the newer versions compacting no longer resets the AutoNumber value unless
the table has all records deleted.
 
Rick is right, you’ll have to delete all records and compact and repair.

If you want to retain your existing record 4 digits, delete or edit the 8
digits to 4 digits.
1. Copy and paste this table to a temp table (structure and data).
2. Compact and repair.
3. Copy temp table records into the table.
Auto number will auto increment according to last highest number.

Or

As Serious Sam would say,

You could do the easy way or my way.
They are basically the same thing!
I have delete all 8 digit records and then did another compact and repair to
no avail.

the database is secured and I am the only one with admin access.

should i try to delete the last 4 digit number? 9402 then compact and repair?
Maybe you have a code in an event or you had accidentally inserted a record
into your table with a 8 digit number. The autonumber will auto increment
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Will this reseed the whole table (restart from zero) this is a TroubleTicket
Database and if a customer call referensing a Ticket# that has been
renumbered it could create problems.

Thx
 
It re-seeds from the value you supply to the (optional) third parameter
lngNewStartValue. If you do not supply a value it will reseed the column to
so that it starts with 1. You also will need to set a reference to the
Microsoft ADOX library for this code to function.

Ron W
 
Thx, I think I'll try the code.
I'm not much to coding inside MS Access as yet.
So how would I run the code that I have from you?
After day 3 I'm up to about 90 extra Tickets with an 8 digit number.
 
Bobcat,

How is it going? Whatever it is, you still need to move your existing records
to a temp table and in that temp table, change the "AutoNumber" to "Number".
Edit the 8 digits to 4 ditgits. Delete the records after creating the temp
table. Do a "Compact and Repair" then Copy and Paste Append from temp table
into your table.

If you want to use Ron's code, copy and paste it into a standard module and
in the Immediate Window, key in ResetAutoNumberSeed "YourTableName",
"YourColumnName", , "YourNewIncrementValue"

"YourNewIncrementValue" is the last record number + 1.

Note: with the edited 4 digit numbers before running this code.
 
Back
Top