[AccessD] Custom autonumbering

If this was in SQL, I'd create a udf, if Access a Function to access the
second table and manipulate the date (or whatever) to your liking.

In the function, Insert a record into a table, then return the value:

If TSQL, using @@IDENTITY would return the last inserted PKID (so much
easier)

if VBA: insert a username or ID into a table such as:

tblNaturalKeyGenerator
PKID
EntryUserID (or UserName)
entryDate ( DefaultValue = Now() )

Then SELECT PKID, Max(EntryDate) tblNaturalKeyGenerator FROM  WHERE UserID =
MyUserID (or name)

Get that PKID and do your concatenation to the date or whatever you wish.

If tblNaturalKeyGenerator had 101 records in it and
Jim (ID 15) inserts a record a split second before Bob (User 23)

Both insertions take place (PKID's 102 & 103), but selecting the max PKID by
the UserName or ID will give you their respective PKID.

intOrderNumber_Runtine = FORMAT(NOW(),"YYYYMMDD") & RIGHT("0000" &
DMAX("PKID","tblNaturalKeyGenerator ","[UserID]= 23"),4)
=200907010103



On Wed, Jul 1, 2009 at 5:13 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:

> 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
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>