next autonumber to be inserted in MS 2

[kohdesmond]

what can we do if we have to get the next autonumber to be inserted {before inserting the record} in MS access

It is a simple think to get the max(id) + 1.But if there is some record deleted then this will not work correctly

for instance i have records

id S_N
11 a
23 b
31 c
45 d

then if delete 45 my record becomes

id S_N
11 a
23 b
31 c

now when i get max(id) +1 it will return 32 {rather then 46 which i want's to retrieve}

Is there any way that i can get the next autonumber to be inserted prior to insertion of the record ?

 

[dhookom]

I don't think there is a method and generally have never found a need to determine the next number.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

As Duane said, you can't, and shouldn't! Not that it really matters, but what if you decide to dump the record rather than save it? You can't if you've already inserted the AutoNumber! Once data is "inserted" the record is saved.

Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.

For the kind of thing I suspect you're trying to do, you need to use an auto-incrementing number hack such as one of these.

The first code here would be for an IDNumber that is defined in the table as Text datatype. "Number" fields that aren't used for math really should be defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

If you are using a Numerical field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = 1
  Else
   Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  End If
End If
End Sub

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[boblarson]

Another idea -

Use a numbers table to store the numbers you create and base your DMax off of that. If one of the numbers used is deleted, it won't matter because you've a table that just keeps the numbers as they are generated. I can't remember which MVP on Utter Access I heard that from, but it was one of them.

Bob Larson
Access MVP
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[kohdesmond]

Thx Guys for replying my post,missinglinq how do u write the VB code in Access ?