[AccessD] Custom autonumbering

Hi Asger

As this is Access, why not use DAO which is ultra-fast?

I brushed up a function we used many years ago for a similar purpose. It simply adds 1 to obtain the next number. Of course, it can be modified to use any other calculation for the next sequential number and formatting for string expressions can be applied as well if the sequential number is alpha-numeric.

In general, the use of DMax or the like is fine in a single-user environment but it has the drawback that once the table is read and the number is found, the table is released while you calculate the next number. When you wish to save this, anything can have happened to the table and you may end up with duplicate numbers or cumbersome error handling routines.


<code>
Public Function GetSequentialNo() As Long

' Creates and returns a new sequential number in table tblSequential.
' Works in a multi-user environment as well.
'
' Table schema:
'   Id: AutoNumber, primary key.
'   SequentialNo: Long, unique key.
'
' 2009-07-02. Cactus Data ApS, CPH.

  ' Initial value for the sequential number.
  ' Only used once - when the table is empty.
  Const clngNoInitial As Long = 1000
  
  Dim dbs       As DAO.Database
  Dim rst       As DAO.Recordset
  
  Dim strSQL    As String
  Dim booEmpty  As Boolean
  Dim lngIdLast As Long
  Dim lngIdNext As Long
  Dim lngNo     As Long
  
  strSQL = "Select Top 1 * From tblSequential Order By Id Desc"
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)
  
  With rst
    booEmpty = Not CBool(.RecordCount)
    If Not booEmpty Then
      ' Read last used Id and sequential number.
      lngIdLast = .Fields("Id").Value
      lngNo = .Fields("SequentialNo").Value
    End If
  ' To demonstrate multi-user behaviour with asynchronous ("overlapping")
  ' creation of records, open two or more instances of the database and
  ' uncomment this line:
  ' Stop
    .AddNew
      If Not booEmpty Then
        ' Calculate next sequential number.
        lngIdNext = .Fields("Id").Value
        lngNo = lngNo + lngIdNext - lngIdLast
      Else
        ' Use initial sequential number.
        lngNo = clngNoInitial
      End If
      ' Store the new sequential number.
      .Fields("SequentialNo").Value = lngNo
    .Update
    .Close
  End With
  
  Set rst = Nothing
  Set dbs = Nothing
  
  GetSequentialNo = lngNo

End Function

</code>

For a high-speed system you would consider making the dbs and rst objects static.

/gustav


>>> ab-mi at post3.tele.dk 02-07-2009 02:13 >>>
David, I always use an AutoNumber as my PKID, and not my generated number.
My original posting was:
/*
Using Autonumber for surrogate primary keys, I often need to manage custom
autonumbering for alternate unique keys, e.g. for order numbers.
I know this subject has been discussed several times before, but I couldn't
find exactly what I was looking for in the archive.
How do you manage custom autonumbering?

I do it this way, which may not be the most efficient, so I'm calling for
other suggestions:
1. On the form I set the property DefaultValue at design-time for the
control holding the custom autonumbering field to:
=DMAX(<field>,<table>) + 1
This will do in single-user environments, but will invite duplicate values
in multi-user environments.
So I use this run-time code in addition to the design-time DefaultValue:
2. Assuming the custom autonumbering field is called OrderNumber in a table
called tblOrders:
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim intOrderNumber_Default As Integer
   Dim intOrderNumber_RunTime As Integer
   If NewRecord Then
      intOrderNumber_Default = OrderNumber.Value
      intOrderNumber_Runtine = DMAX("OrderNumber","tblOrders") + 1
      If intOrderNumber_Runtime <> intOrderNumber_Default Then
         OrderNumber.Value = intOrderNumber_Runtime
         MsgBox "Another user has created a new order with the number " & _
                intOrderNumber_Default & vbNewline & _
                "Your order has got the number " & intOrderNumber_Runtime
      End If
   End If
End Sub
Do you think this solution has any pitfalls, or do you just have another
more efficient/intelligent custom autonumbering?
/*

Your suggestion using a second table equals Drew's proposal, but it doesn't
satisfy my requirement of a customisable alternate key, on which I was not
enough explicit in my first posting.

Asger


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
Sendt: 2. juli 2009 01:28
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Custom autonumbering

Asger, just make sure to use an autonumber as your PKID, not your generated number.

If at some later point in life, the criteria changes for the number, so be it. You PKs & FKs will love you for it.

You can create a table for the sequence number by inserting a value into a record with only an Autonumber PK and some other field.

I had a request where the facility request numbers were the date and a sequence of 3 digits ( 20090701001-20090701999)

I created a sequence table and have a job that truncated that table each night at midnight.

You need to know what happens if a number is in process of being created and a second user creates a new number but the first user/process cancels.

Will you leave a hole?

Will you save the record and flag it as cancelled?

What if user 2 fnishes before user 1, does it matter if they are out of sequence, yet the numbers are sequential?

David