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

Welcome to UtterAccess! Please ( Login   or   Register )

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

UtterAccess VIP
Posts: 2,128
Joined: 20-November 03
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: 426
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th April 2018 - 11:33 AM