Consecutive Numbers

[Tieval]

I have a database which has a table in the back end that stores the last used order number. When you generate a new purchase order it takes this number and adds one to generate an order number on a form. Once everything is done you close the form, save all data and roll the last used order number on by one.

Last Number = A005 -> New Order Number = A006 -> Close Form -> Last Number = A006 -> New Order Number = A007

This works a treat until two users generate orders at the same time, is there an easy way to lock the table and tell user two to wait a minute.

 

[bob fitz]

This link may help: https://support.office.com/en-gb/article/RecordLocks-Property-6ca29bbb-8824-4671-8087-97fe0568019a

 

[Minty]

Add a lock flag that you set at the beginning of the transaction which prevents other orders being processed. Un-set the flag once the order is completed, Messy and not multi-user friendly bit the only real solution.

This is why an Autonumber PK is so handy - it takes care of all this for you. Just don't get stressed out by missing numbers if you delete some records.

 

[Tieval]

I have a table of order items which is fully primary keyed, however I adopted a single order number field so I might have
100001 A007
100002 A007
100003 A007
100004 A008
100005 A009

Using the rolling last number table somebody might start to generate an order and when they have finished roll the number on, by that time another person may have started and order with the same number as it will not roll on until an order generation is complete.

I can now lock the table as the order form is based on the last used number field and have tested that the table is locked whilst the form is open but need to tell a user when they open the form on another system that the table is already in use.

Any clues on how to do this?

 

[Minty]

Then you should also have an order header table with another Autonumber primary key and use that as the link. If you want to display it with an "A00000" formatting simply use a format command on the form/reports you see it on.

 

[The_Doc_Man]

There are several schools of thought on this problem.

One school says to find the maximum number and create a new record with that number + 1, then SAVE IT NOW even though it is blank. Update it in its mostly blank state so that you take out a reservation on the number. The idea is that you leave open a very small window of opportunity for a number collision, but if you do the operation quickly enough, that window is VERY small.

Another school says, just make that field a "No Dups" field and if a trap occurs while allocating it, just have the trap handler reset the (now non-unique) field to another number and have it try to store that record again. Don't worry about allocating numbers in a specific order completely. Let Access do some of that worrying for you.

A third school asks "Why does this number have to be contiguously and monotonically incremented?" What is so special about it that non-contiguous numbering kills your business model? You could have record deletions that lead to numbering gaps if you allocate a record and your customer says, "Oh, darn - I'll have to cancel this now and come back later when I have enough money in the bank" (or something similar.)

Further, you might face a case where if you have two clerks with two different sized orders, even though the one with the bigger order started first, the one with the smaller order will FINISH first, and you could thus end up with the interesting case that if you view the orders sorted by closing timestamp, the order numbers will not be monotonic, and if you sort by order number, the transaction finishing times won't be in order. And you can't really stop that completely.

 

[Tieval]

Thanks Doc Man and Minty, my problem was picking a number from the table when I opened a form and rolling it on when I closed the form, if a user took their time all sorts of other things could go wrong when others interacted.

I moved to a system of adding a record to the number table on entry and using a form of it's primary key as my order number, this works fine

 

[The_Doc_Man]

Just remember, deletions after the fact WILL leave gaps sometimes. As long as you can live with those gaps, you will be fine.

 

[Tieval]

Thank you Minty and the Doc Man, I merely wanted unique order numbers and have no issue with consecutive numbers and missing numbers so creating and saving a number from a primary key at form load time sorts everything perfectly.