Prevent Autonumber Increment If record not Saved (1 Viewer)

[usm01]

I am new to Database.
i have a form with an Autonumber field and some other fields with validation rules.
If user cancels or closes the form or if record is not saved, i want prevent Autonumber from incrementing. It should Increment if a record is saved with all validation rules fulfilled.

In short I want no gaps Between autonumber Prospectively.

Plz Help.

 

[John Big Booty]

Firstly Welcome to the forum

The autonumber, should not be relied upon to do anything other than provide a unique identifier for each record.

If you require sequential numbering for some purpose, then you will need to look at implementing your own code to do this.

Look into the Dmax() and search this forum as this is a subject that comes up on a regular basis.

 

[MarkK]

Hi, and welcome to the forum.
Autonumber doesn't work that way. Autonumber guarantees a unique ID, not a consecutive ID. In a multi-user environment it has to be impossible for two concurrent users to save a record with the same ID, so an Autonumber is only ever available once for the lifetime of the table.
You can 'roll your own' consecutive number using DMax() + 1
Cheers,

 

[MarkK]

Almost verbatim.
And isn't that 'Boo-tay?'
Cheers,

 

[John Big Booty]

almost verbatim.
And isn't that 'boo-tay?'
cheers,

snap

 

[usm01]

Thanks for quick reply .
I will search the forum.

 

[missinglinq]

Here's a typical Auto-incrementing Number hack. The first code here would be for an IDNumber that is defined in the table as Text datatype.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim StartName as Long

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

Here's the same code for an IDNumber defined as Numerical:

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

You'll just need to place your validation code in the BeforeUpdate event before the auto-incrementing code, and have the auto-incrementing code placed so that it will not be executed if the record is dumped.

 

[usm01]

It Works.
Thanks For Helping.

 

[missinglinq]

Glad we could help!

 

[rayhan]

Here's a typical Auto-incrementing Number hack. The first code here would be for an IDNumber that is defined in the table as Text datatype.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim StartName as Long

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

Here's the same code for an IDNumber defined as Numerical:

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

You'll just need to place your validation code in the BeforeUpdate event before the auto-incrementing code, and have the auto-incrementing code placed so that it will not be executed if the record is dumped.

I am using MS Access 2013.
This code not work in my data base.
Please Submit a sample File.
Please Help Me.
Table name SrRegister, Field name Record_Number, Data Type - Number

 

[Minty]

Doesn't work is no help to us - what is your code and what is the error message you are getting?

 

[rayhan]

Doesn't work is no help to us - what is your code and what is the error message you are getting?

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

why This code not work?
pls help me.

 

[Minty]

And what error message.? Because that should work.

Sent from my SM-G955F using Tapatalk

 

[rayhan]

And what error message.? Because that should work.

Sent from my SM-G955F using Tapatalk

When I Open The Form the Record_Number Field always Show 1.
I want form will open new unique sequence record number.
If last record _Number is 5.
When again i will open the form i want to show new record_number will 6 in record_number Field.
Pls help me.

 

[rayhan]

When I Open The Form the Record_Number Field always Show 1.
I want form will open new unique sequence record number.
If last record _Number is 5.
When again i will open the form i want new record_number will 6 in record_number Field.
Pls help me.

I Have attached my file here

 

[Minty]

The code as used will only get the latest number when the record is saved, not before. What if two people open the record at the same time?