Can we add a sequential + unique article number other than ID in Multiuser front end environment (1 Viewer)

Ihk

Member
Local time
Today, 12:51
Joined
Apr 7, 2020
Messages
280
Hey, I am looking for a way out have article number which must have to be
1) Unique
2) Sequential
3) other than ID field (Autonumber), if sequence is missing, then missing number can not be reused.
3) this can be 4-5 digits, can includes alphabets as prefix.

Environment is multiuser with having their own front end that's why it is very tricky. DMAX wont work in that case. For example if 5 users at the same time are data entering.
My Idea, How can this be possible, (May be you can give me different suggestion):
1) It might not have control on form
2) directly inside query which is linked with form to enter data in table.
3) Query keeps refreshing refreshing records from table, to know which is the last article number.
4) If someone saves records from form, query the inserts sequential number --- this way even if multiple users are entering data, Query will take care of it every second. and enter new sequential number.
How is it possible???

I have an other ways of unique article entery system but thats not unique , still want to share, may be for someone it is of interest. Article number is entered on YearMonthDayhrMinSecond (for example: 20210925125609) , It will always be unique (more than 99.95% chances)

Thanks in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:51
Joined
Oct 29, 2018
Messages
21,357
Have you tried using a separate table to store the last or next sequential number?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Feb 19, 2013
Messages
16,553
you can use dmax - but don't get the value until the record is about to be saved (i.e. use the form before insert event) - and the event needs to include error handling because if two users try to update at exactly the same time - the first to go through gets the max value, the second user will get an error because that value now exists, so the error handler needs to get a new max value before attempting to insert again - and keep repeating until the record is saved.

There should be no reason the user needs to know the value to be assigned until the record is saved.,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:51
Joined
May 7, 2009
Messages
19,169
you will always have a "missing" series on your number because you are using "seconds".
you do not want to immediately save the unique number, in case the data entry operator
decided to cancel the whole entry.

what you will initially "show" to the user is a generated number (add "provisionary" to a label control).
it kind of whose saved win first.
before saving the record, enquire if someone has beat you on saving same number.
if none, add a Lock on a semaphore table, then save your record.
the system is consistent, everybody must lock the semaphore table before saving a record.
so only one can have a lock on this table.
you must continually check if the number was saved and that you have a lock.
when the number is saved and unique, fine.
when not you get another unique number and save it.
replace the number on the form with the "permanent" number (removing "provisionary" from the label).
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Sep 12, 2006
Messages
15,613
Personally, because of the issues just mentioned by @arnelgp I find it much easier to get the next number from a next number control table, rather than using a dmax. You still need to leave obtaining the number until you are committing the record, so you don't "burn" the number.
 

Ihk

Member
Local time
Today, 12:51
Joined
Apr 7, 2020
Messages
280
yes, like a "pre-printed" ticket, you only need to pick the available un-used ticket.
That will be be nice, if any demo database is available for my better understanding. thank you.
 

Users who are viewing this thread

Top Bottom