Auto Number Skips a Number

[dvannoy]

I have this wierd problem...I have an auto number that i use on a form. when i open the form and start adding records, the auto number will count in order...

once the form gets closed and then opened back up to add more records, the auto number skips a number..

e.g. if the last record has an auto number of 10..once the form opens backup and a new record gets added the auto number jumps to 12.

There are no wierd settings on the auto number..cant figure out why it's doing that..

can someone help?

Thanks


DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[AlexMidd]

This sometimes happens on my databases but it is because I don't always use the automatic addition of records, i.e. where you open up a form for new entry and when you close it you have your new record. Soemtimes I use code, i.e. AddNew etc and this tends to cause a skip in the autonumbers.

It could be something like this in yours. Also, if you start to enter a new record with an autonumber then abort it, when you add the next record it has skipped a number, i.e. it starts from the next number after the one you would have added if you had not aborted. It could be that each time you add a new record while the form is open, the next autonumber is registering, but when you close the form it aborts the latest addition, hence the skip.

You would need to carry out intensive debugging on your forms and/or any code being actioned to track this down. Basically, I never use autonumbers for any fields that are for public viewing and therefore need to be in order with no gaps for the sake of tidiness. Alex Middleton

 

[dvannoy]

The reason i am using an autonumber for the users to see is, they are logging in samples and need that number as a reference...is there a way to create a field and have it insert a number like an autonumber??

thanks DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[AlexMidd]

There certainly is. Basically you create a field and write code to add a new number each time a new record is created. This involves counting all the existing records then incrementing the number and allocating it to the field, either directly or via a control such as a text box.

It is more complicated to set up than an autonumber, but more reliable. Alex Middleton

 

[SyedAqif]

Hi

Yes Alex is right...autonumber is an sequence so the sequence always runs everytime ur system goes into a new record mode....so u can do something like write a code behind add new button or any even which fires when u create a new record

Dim mydb as database
Dim myrs as recordset

set mydb=currentdb()
Set myrs=mydb.openrecordset(select max(SNo)+1 As Next_Sno from mytable)

-- Your procedure for adding record goes here

txtSNo.Value = myrs.Fields("Next_Sno&quot

I hope this will work for you.

Cheers!
Aqif

 

[Aivars]

Hi!

I would not use the autonumber because lot of problems proceed when you need to export, import or consolidate data etc. I don't use more autonumber long ago. In lieu of it I set new number in procedure Form_BeforeInsert on forms.

Example from my application:

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(DMax("HistNo", "SRH", "PersonalCode='" & Me![cboPersonalCode] & "'&quot) Then
Me![HistNo] = 1
Else
Me![HistNo] = DMax("HistNo", "SRH", "PersonalCode='" & Me![cboPersonalCode] & "'&quot + 1
End If
End Sub

Hereto in such case you can find "interstices" (numbers of deleted records) in the table.

Aivars

 

[dvannoy]

Aivars thanks for the code..
It works for the first record but then gives me an error message about type mismatch..



DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[Aivars]

Hi, DVannoy !

In my case fields HistNo and PersonalCode are defined as Dublicate OK and both have Primary Key index. If you have only one field with primary key you may set new value without any criteria.

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("HistNo", "SRH&quot=0 Then
Me![HistNo] = 1
Else
Me![HistNo] = DMax("HistNo", "SRH&quot + 1
End If
End Sub

Aivars