autonumber field on a form (1 Viewer)

[faz88]

I have a bound form with an autonumber field for booking ID. Is there a way for the autonumber to generate once the save button is selected, rather than the autonumber being generated once the form loads?

 

[ColinEssex]

If you do a search you will find we have explained all this today on another thread.

You ONLY use the autonumber for linking tables internally, you do NOT use it for generating a booking ID or invoice number or whatever.

Please do a search to establish the correct way to generate the number you need.

Col

 

[ByteMyzer]

I have a bound form with an autonumber field for booking ID. Is there a way for the autonumber to generate once the save button is selected, rather than the autonumber being generated once the form loads?

There is if your back-end data engine is something like SQL Server, where the autonumber value is not generated until the record is saved. If you are using a typical Jet-Engine database (MDB file), then there is not.

However, if you wish to implement a Custom Counter to generate your own numbers, you may find the following links useful:

How To Implement Multi-user Custom Counters in DAO 3.5 (NOTE: this method also works with DAO 3.6)

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1 (NOTE: this method also works with later versions of ADO)

 

[missinglinq]

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.

What you need is to generate an Auto-incrementing number for you ID field. 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. "Number" fields that aren't used for math really should be defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim StartName as Long
[COLOR="Red"]'Place all other code for this event here[/COLOR]
If Me.NewRecord Then
   If RecordsetClone.RecordCount = 0 Then
    StartNumber = InputBox("What Number Would You Like To Start With?")
    Me.IDNumber = StartNumber
  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)

[COLOR="Red"]'Place all other code for this event here[/COLOR]
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
    StartNumber = InputBox("What Number Would You Like To Start With?")
    Me.IDNumber = StartNumber
  Else
    Me.IDNumber = DMax("[IDNumber]", "YourTableName") + 1
  End If
End If
End Sub

By placing the code in the Form_BeforeUpdate event, it will be generated at the last moment before the record is saved. Placing it after other code in the event, if any, such as validation code, further assures this.