my application is an Access 2000 front-end connected to a SQL 2000 database
back-end

My problem is when two or more users are editing the same record at the same
time, the first person who exits the record gets their changes saved,
everyone else's changes are droped.

I would like to know if there is a way to lock a record for editing.
a.  When one person opens a record, that record is available to be edited by
that person only
b.  When the second person opens the same record, they are only allowed to
view the record. (maybe a message appears that the record is currently being
edited by another user)

****note ****  I've tested it and found that even if I am the first person
to open a record and start editing it ... if a second person opens the
record and makes any changes then closes before I close, their changes are
kept and mine are droped.  I get one off two possible messages:

The data has been changed.
Another user edited this record and saved the changes before you attempted
to save your changes.
Re-edit the record

or

This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make changes.
************