Problem With AutoNumber

[accesser]

I have a problem with autonumber
This is problem is that the autonumber is continuing increasing even if all the table records deleted. What I want is that the autonumber is incremented start from zero and not from the its last counter before the deletion process.
For Example, If My Table has 5 records, the autonumber should start counting from zero, then incremented by 1 as new record addes and so on. If the contents (records) of the table deleted, the new time where new record is addes, the autonumber should start counting from zero not from 6 where the value of autonumber before deletion made.

Please help me

 

[KenHigg]

I would suggest you let the autonumber fld do it's thing and if you need a count type fld, make a seperate fld or calculate it on the fly...

kh

 

[TheEvilBankers]

Delete the information and compact and repair the database. It should go back to 1 as the first record.

K

 

[gem1204]

If you are deleting all the records from your table you can reset the autonumber field by running an append query and appending the number 1 to your autonumber field. this is the sql
INSERT INTO Table1 ( AutoField ) SELECT 1

you could actually set the autonumber beginning value to anything you want by setting the first value of the autonumber field.

 

[Daffersd]

Autonumber question

Sorry to barge in on this thread, but as I have a related question!

I have an Autonumber field that suddenly decided to start using numbers from 127205489 while my previous last record is 9916. I want to re-set it back to 9917, but do not know how.

I tried compact and repair but it did not work!

My issue is that the field is linked elsewhere and it would be a real swine to delete the field and re-set.

The odd thing was that a new record was in the database with chinese characters with the number 127205487 So I was wondering if I had been hacked.

 

[Daffersd]

Located some excellent instructions on this excellent forum on how to re-set the Auto-number, works perfectly, thnx.

 

[Daffersd]

Actually this does not work in my case!!!

http://www.microsoft-accesssolutions.co.uk/reset_autonumber.htm

Any other ideas?

 

[KenHigg]

Daffersd, I think you're trying to use the auto number feature in a way that I really don't think it was meant to be used. Sounds like you need a way to 'count' records in some manner. The auto number is really used to just create a unique identifier for a record. It is more or less coincidental that at times they can be used to gauge the number of records in a table...

If it is a record count/qty type thing you are after, I would suggest you do a new fld and maybe populate it by using dcount(), or figure out how to do a count on the fly...

Just my humble opinion
Ken

 

[Daffersd]

Ken, thanks for the input, I was not bothered about using it as a count, but I did not want to have a reference of 127205489 when there was only 9916 records. It suddenly happened, don't know why.

Anyway I have sorted it out, I copied the data and structure to a new temp table, deleted the old table, then copied the structure as the old table name then ran an append query to add the data and then Compacted and Repaired and it started coming out with autonumbers from 9917.

I have no idea why it suddenly started using such a huge number and had Chinese characters, very odd.

 

[KenHigg]

Glad you got it worked out...

Sounds like something got corrupted.

kh