Autonumber sequence incorrect

[aziz rasul]

I have a table which contains an index on 5 fields.

Using code I create an autonumber field.

I want the sequence of the autonumber values to be sequential based on the sorting of the data created by the index. However when I create the autonumber field, I do not get the right autonumber value in the right sequence e.g. where the first record should have the autonumber value of 1, it takes on the value of 65.

How do I ensure that the sequence is correct?

 

[GaryPanic]

be careful here -autonumber is great as far as it goes - it will give you a unique number and in assending order -however it can (and does) skip numbers for various reasons
if you want a numbering system that increases by 1 then check the samples on this - various methods of doing this ....
autonumber just gets the next number - if it has processed through some numbers then it will carry on - -

you can reset the auto number system (again check samples on how to do this)

 

[Rabbie]

Autonumbers do not guarantee to start at one nor do they guarantee to increment by 1 each time, The only guarantee is that they will be unique.

See this link for help on resetting the Autonumber

 

[Uncle Gizmo]

I think I have solved this problem by creating a new table (structure only) exactly the same as the old table, and then appending the data into the new table from the old table sorted in the order that you want. I think this worked, but can't be absolutely sure.

 

[Uncle Gizmo]

>>>The only guarantee is that they will be unique<<<<

I recall reading somewhere recently that you can occasionally get duplicate numbers even in the supposedly unique ID field! I wish I could remember where I read it.

 

[Rabbie]

I think I have solved this problem by creating a new table (structure only) exactly the same asthe old table, and then appending the data into the new table from the old table sorted in the order that you want. I think this worked, but can't be absolutely sure.

This will work.

In earlier versions of Access you can reset the autonumber by doing a Compact/repair. This doesnt work in later versions.

 

[boblarson]

This will work.

In earlier versions of Access you can reset the autonumber by doing a Compact/repair. This doesnt work in later versions.

Haven't tried it in 2007, but it works in 2003.

 

[Rabbie]

Haven't tried it in 2007, but it works in 2003.

You must have the magic touch. It didn't work for me on A2002 but I was doing it on the BE database if that can make a difference.

 

[GaryPanic]

heres my code for this in A2000

Dim NextNo As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT NextNum FROM tblnextnum")
NextNo = rs!nextnum + 1
'now update the table
rs.Edit
rs!nextnum = NextNo
rs.Update
rs.Close
Set rs = Nothing



Me.lastinvoiceno = NextNo



it gets a number from a table "tblnextnum" and from a field called "NextNum"

this works pretty well- however even this may occasion give a duplicate if two people push the button at excatly the same time -if its for 1 user then it won't happen - also gives you the ability to reset numbers

Note - I pinched this - so credit to someone else --gary

 

[boblarson]

this works pretty well- however even this may occasion give a duplicate if two people push the button at excatly the same time

Pat Hartman suggests assigning the new number at the last possible moment, typically the Before Update event of the Form. Then you are even less likely to have conflicts and/or gaps caused by someone taking the number and then canceling.

 

[GaryPanic]

my version (as shown ) works pretty well on a system with 8-10 users invoicing (thats what it was for) and in 6-7 years (about 40,000 invoices) -- 6 duplicates - i could live with this.

Bob - yeah read this - but this version is on a push of a button - so you have actively got to want to do it- i had this on a different form - so you had to go to a transaction form - then i had it check certain fields (Insurance , so inception date, renewal date and other stuff and only if the record was complete would it run the invoicing process )

but hey .. its whatever you need it to do