G'day Lynn,
Here's an updated post from a subsequent discussion.
At 21:36 10/07/01 -0600, you wrote:
>Due to some programming constraints, I have to do my own autonumbering
>field management - i.e. not using the standard autonumbered column
>method. I can do this with no problem on a stand-alone application, but I
>am concerned
>with potential problems if the application is adapted to operate on a
>network in multi-user mode.
>
>I remember a couple of years ago a pretty good discussion on methods for
>doing one's own autonumber routine that avoids duplicate numbers being
>assigned in a multi-user situation. I had this discussion printed out at
>one time, but have lost it. Can some of you give me some refresher
>hints/tips?
I too read the articles on autonumbering with great interest as I was
having ROWLOCK error messages using the technique I was applying at the
time.
Unfortunately in the articles posted I found no one, simple solution which
would solve all requirements, each had their merits appropriate to a given
situation. The thing to do is to write out your requirements for a given
situation then understand the ramifications of each of the techniques and
assess their suitability to task.
The inbuilt autonum feature solves the quick and easy requirement where no
other requirements exist. (Such as code tables using numbers.)
I found a great deal of merit in the code below which is an adaption of
that posted by Bill Perry.
It is quick, easy and effective in a multi-user situation, it does not
require row locking so does not generate inappropriate error messages but
does leave missing numbers if rows are not saved or deleted.
SET ERROR VARIABLE vError
SET VARIABLE vNextID INTEGER = NULL
WHILE #TIME IS NOT NULL THEN
SET VAR vNextID = NewRowNumb +
IN NextRowNumber
UPDATE NextRowNumber SET +
NextRowNumb = (NextRowNumb + 1) +
WHERE NextRowNumb = .vNextID
IF vError = 0 THEN
BREAK
ENDIF
ENDW
CLEAR VAR vError
RETURN
I have this in a block called SetRowID and called from various app files
when required.
The table NextRowNumber has a column NextRowNumb which stores the next
number to be used.
After obtaining the next ID the code
INSERT INTO TranHeader (HeaderID,....) +
VALUES (.vNextID,....)
then
INSERT INTO TranDetail (HeaderID,....) +
VALUES (.vNextID,....)
I has been working for me ever since Bill posted it... many thanks to him.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tom,
I'm glad you found it useful. I have used variations of this for over ten
years, and I have never known it to break. It is the basis for all of my
code requiring sequential numbers, however, as you mentioned, it does not
save or recover "lost" numbers by itself. I wish I had a single universal
solution, but it has been my experience that system/user requirements/usage
require different solutions.
Bill P.
Warmest regards,
Tom Grimshaw
coy: Just For You Software
tel: 612 9552 3311
fax: 612 9566 2164
mobile: 0414 675 903
post: PO Box 470 Glebe NSW 2037 Australia
street: 3/66 Wentworth Park Rd Glebe NSW 2037
email: [EMAIL PROTECTED]
web: www.just4usoftware.com.au
This email and any files transmitted with it are confidential to the
intended recipient and may be privileged. If you have received this email
inadvertently or you are not the intended recipient, you may not
disseminate, distribute, copy or in any way rely on it. Further, you should
notify the sender immediately and delete the email from your computer.
Whilst we have taken precautions to alert us to the presence of computer
viruses, we cannot guarantee that this email and any files transmitted with
it are free from such viruses.
