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    
 
   
jinky44
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:
CODE
'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.
HTH
Jinky
Attached File(s)
Attached File  GenNxtNbr.zip ( 21.66K ) Number of downloads: 349
 
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: 17th December 2014 - 10:58 PM