multiuser custom counter conflict

[paul]

Happy Monday,
I referenced an article (210194) in Knowledge Base: How
to Create A Multiuser Custom Counter, it was running
perfect. Until this Morning, after I split the application
to Front end & Back end, the counter table when open by
two users the same time, it created duplicated value. This
is how it open the counter table:
rs.Open "CounterTable", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
how can I solve the conflict? or how to take care this in
the error routine?
Thanks for your input.

 

[Allen Browne]

Paul, you may be able to avoid the problem much of the time by not calling
the function until Form_BeforeUpdate instead of the command button's Click
event. This is the very last moment before the record is saved that the
number can be assigned, so it would reduce the frequency of the problem.

The only real solution, though, is to create a table containing the highest
assigned counter number, and lock it until the new record has been changed.
Every table you do this with needs its own counter table, so users can
manage to update without interferring too much. The sequence is then to use
the last possible moment, lock the counter table exclusively in code, grab
the next counter number, assign it to your table, save the record, and then
unlock the counter table.