Auto Number is higher than total number of records

[benny7]

Hi

I've got a table which is has a key auto number field. It appears that some corrupt entries may be in the table as only 1 record shows but the auto number is now currently at number 7. How do I remove the other entries. I've tried compact and repair databases, no luck.

Thanks

 

[PHV]

You should not consider an AutoNumber field as a record counter.
It is an unique number and nothing else.
Each time you cancel an insert in your data entry form the assigned autonumber is lost.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

PHV is correct.

Autonumber should only be used to indentify the record, and is not refelctive of the number of records.

Try this...
Start entering a new record. Your autnumber appears - take note of the number. Now hit the ESC escape key twice. The the data entered along with the autnumber disappears. Now enter another new record. The autnumber will be generated again, but will be different than the autnumber entered the first time.

You database is on a network, try this...
Start entering a record on one computer. Leave the record incomplete. Then enter another new record on another computer. Notice anything funny with the autnumbers generated on each computer?

Autonumbers are fine -- it is a very useful way in generating a unique primary key.

Wants some fun? (Once done, you can not change this so do this on a test or a copy of your table) Open your table in design and select the primary autonumber key. In the Field Properties" section, you will be able to change the "New value" from "Increment" to "Random". For small shared databases, I always use random.

Want even more fun? Instead of using the Longer interger, you can specify "Replication ID" -- this is a huge "number". The chances of generating a duplicate, random ReplicationID are very, very small.

Richard

Richard

 

[ZmrAbdulla]

And if you want the recordnumber to be displayed on a form then place label on the form and OnCurrentEvent of the form

Label1.Caption = Me.Recordset.AbsolutePosition + 1 & " of " & Me.Recordset.RecordCount

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[Golom]

Re: ZmrAbdulla's suggestion.

AbsolutePosition does work for a recordset but note that it just identifies the record position in the current ordering for that recordset. That is, if you use a different order by clause or different filter conditions then the record at a particular AbsolutePosition will change. You cannot use the AbsolutePosition as a unique pointer to a record in the base table(s) from which the recordset is created.

 

[SQLSister]

If you want to be able to know the order records were inserted and be able to use ablsulte positon to find the record number of the whole table, then add a field called DAteCreated and poplate it with as default value of now(). Then you can oder by this field and you will see the records inteh oder they were inserted and use absolute position to create a fake recordnumber.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ZmrAbdulla]

Thank Golom & SQLSister for correcting me...

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me