[AccessD] Custom autonumbering

Asger Blond ab-mi at post3.tele.dk
Thu Jun 25 19:30:57 CDT 2009


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?

Asger





More information about the AccessD mailing list