Serial or sequential number in MS Access in multiuser environment (1 Viewer)

Ihk

Member
Local time
Today, 03:57
Joined
Apr 7, 2020
Messages
197
I have been looking for Serial or sequential number in MS Access in multiuser environment. I could not find solution. Many people were suggesting DMAX method on after update. But in a multiuser environment it failed.
Remember: ( It is not ID (Auto number), which is not sequential because many numbers are missed and cant be reused in multiuser environment. ID as unique identifier will be still there), I am talking an additional field for sequential number / article number / serial number etc.

Wanted to share my experience, which worked for me. May be helpful for someone.
Just add this DMAX on "before update" event of a form. It will always be sequential in multiuser environment. I never faced problem after this.
Here is how....
Table Field is numbered field, default value is 0
On a form keep that control related to table field (mentioned), as invisible. So user wont see it. otherwise user will see only "0" as default value.

On Form_BeforeUpdate event
Code:
Me.FormcontrolName.Value = Nz(DMax("TableFieldNameBelongingToThatControl", "TableName")) + 1

Hope this will help to get sequential number in multiuser system.
If some one else has better and easy solutions, or sequential number meaning Mix of Alphabets with numbers like abcde1345, are welcome to share their experience.
Note: it is for multiuser environment.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:57
Joined
Feb 28, 2001
Messages
22,486
Rather obviously, this ID number will be used for some kind of auditing. If you had another item to help you with identifying the specific record, look into sub-queries that would allow you to have an "indivisible" transaction, something that might resemble this:

Code:
strSQL = "UPDATE mytable AS M SET M.seqno = 1 + ( SELECT DMax( T.seqno ) FROM mytable as T )  WHERE M.recordkey = " & Me.recordkey & ";"
CurrentDB.Execute strSQL, vbFailOnError

You can read up on subqueries here:


The subquery will in this case return a single value. This works because execution of a single SQL statement is indivisible even if it contains a sub-query. This would be trickier if you don't have a unique key to identify the record.

Search this forum for "custom ID number" to see other solutions that have been offered in the past.

Rather obviously, you found a solution involving the "BeforeUpdate" event. That works too.
 

Ihk

Member
Local time
Today, 03:57
Joined
Apr 7, 2020
Messages
197
Rather obviously, this ID number will be used for some kind of auditing. If you had another item to help you with identifying the specific record, look into sub-queries that would allow you to have an "indivisible" transaction, something that might resemble this:

Code:
strSQL = "UPDATE mytable AS M SET M.seqno = 1 + ( SELECT DMax( T.seqno ) FROM mytable as T )  WHERE M.recordkey = " & Me.recordkey & ";"
CurrentDB.Execute strSQL, vbFailOnError

You can read up on subqueries here:


The subquery will in this case return a single value. This works because execution of a single SQL statement is indivisible even if it contains a sub-query. This would be trickier if you don't have a unique key to identify the record.

Search this forum for "custom ID number" to see other solutions that have been offered in the past.

Rather obviously, you found a solution involving the "BeforeUpdate" event. That works too.
Thank you very much for sharing this. I will go through that link as well.
I am not omitting ID as unique key to identify records. It is still there, plus the rest of the fields. Along with that, Serial number or sequential number is an additional field, which I am talking about.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:57
Joined
Feb 28, 2001
Messages
22,486
Just as a thought, it sounds like something you really don't need in a table. There are queries on this forum that provide ordinal numbering via query but never show it in a table. Of course, a form works equally well off of queries or tables most of the time.
 

Ihk

Member
Local time
Today, 03:57
Joined
Apr 7, 2020
Messages
197
Just as a thought, it sounds like something you really don't need in a table. There are queries on this forum that provide ordinal numbering via query but never show it in a table. Of course, a form works equally well off of queries or tables most of the time.
In my context I needed that in the table, which served as Article number / billing number / etc --- . Thats why I shared, some people might be looking such things like me. ;)
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:57
Joined
Feb 19, 2002
Messages
35,834
What is the business rule that says there can be NO gaps? If there actually is a "no gaps" rule, then you must ensure that records cannot be deleted because a deleted record will create a gap.

You would use the dMax() method to find the highest sequence number and increment the value as the last statement of the BeforeUpdate event. You want to minimize the potential for interference from another user in a multi-user environment. Your code will need to trap the duplicate PK error and go into a save loop. Get the max ID again, and increment it and save the record. Catch the duplicate PK error. You have to decide how many times to loop before exiting.
 

Users who are viewing this thread

Top Bottom