Auto-increment values

[atram]

hello

I have set up the primary key as auto-increment through sql.
coursereference int AUTO_INCREMENT,
I have also entered VBA code for event handler below:

Private Sub Form_AfterInsert()
Me.Requery
DoCmd.GoToRecord , , acLast
End Sub

Every time when I delete the previous value , the next one entered is not decreased by one.
Could you please help how to set it up?

Thanks

 

[Moniker]

The AutoNumber (assuming that's what you mean) will continue to increment regardless of deletions. For example, if you have this:

1
2
3
4
5

And then you delete 5 (or any other number), the next number is still going to be 6.

The "work-around" (sort of) is to do a compact/repair on the DB. This will reset the AutoNumber to the next highest value. However, keep in mind that the point of AutoNumber is to supply a unique value, not necessarily the next logical number. If having the number sequentially ordered is just that important to you, you'll have to manually maintain it, as in:

MyAutoNumber = DMax(MyAutoNumber)+1

~Moniker

 

[atram]

hello
The suggested formula- MyAutoNumber = DMax(MyAutoNumber)+1.
Where can paste it? Please can you explain it to me?
Thanks

 

[ansentry]

Have a look at the attached sample.