UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
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: 26th November 2014 - 01:18 AM