X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

Reply to this topicStart new topic
> Generating sequential numbers for INSERT/UPDATE statements    
post Apr 28 2004, 01:00 AM
Post #1

UtterAccess VIP
Posts: 2,128
From: San Jose, California

Here's a function that I've used in A97, A2000 and A2002 environments:

Since autonumbers should not be used for attempting to sequentially number things like orders, invoices or ..., the logical question is:

"What can I use to generate the next sequential number? I have a shared database with multiple users!"

The most common answer is: "Roll your own code (carefully)".

However, I've found that repeatedly rolling my own code each time I need a "next number" is tedious and results in duplicate coding effort.

So, I came up with a user defined function that I named xGenNxtNbr.

It can be used directly in/with SQL INSERT and UPDATE statements and will attempt to do the insert or update up to 20 times, and will pass back the generated sequence number (or 0 if unsuccessful).

Brief syntax description:
'Example to insert next order number, where customer = 7, customer purchase order = 004-A40B

  Dim x as Long
  Dim strWk as String

  strWk = "INSERT INTO tblOrders(OrdNbr,CustNbr,CustPO) VALUES (xGenNxtNbr(), 7,""2004-A40B"");"

  x = xGenNxtNbr(strWk,"tblOrders","OrdNbr")   'x will be 0 or the generated next_number

'Restriction:  The SQL statement can contain only one instance of xGenNxtNbr().

The attached zipped sample A97 mdb contains the code and a sample form that illustrates its use.

Some key points to note in the code are:

1. The use of a .recordsaffected statement to detect the success or failure of the insert/update operation. It is crucial to the task, since the .execute process does NOT raise an error (in A97, at least) on a duplicate key violation.

2. The On Error statement is used to handle things like sql syntax errors, broken databases, etc.

3. It uses a CurrentDB.execute call, so if you are using your own special "transaction commit/rollback" processing, it may need to be adjusted a bit to fit your needs, but for normal application processing, I've found it to be quite useful.

4. It assumes that the number being generated is a LONG, in the range 1 to 2^31-1.


Attached File(s)
Attached File  GenNxtNbr.zip ( 21.66K ) Number of downloads: 347
Go to the top of the page

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:

RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 25th July 2014 - 12:16 AM