Re: Multi-user Moving to the next unlocked record in a Table

Ok, I hear what you say. You have several opertors all picking a record
from the table, and you don't want two of them to pick the same record
at the same time.

First, Access 2003 does have optional, true, record-level locking. But
personally, I'm not sure I'd want to rely on that.

The problem with setting a flag in the record, is two-fold. (1) You'd
get locking conflicts anyway, when two operators pick the same record
at the same time. (2) What if the db sets a flag & then the db fails?
Maybe that flag will never get un-set, despite the fact that no
operator has actually contacted that record yet.

Here's what I'd try. See if code like the following will successfully
establish a temporary lock on the record, that can be seen by other
operators. ?????? represents the options that you'd have to play-around
with. If this worked, you'd just attach it o a "Lock this record"
button, or somesuch. If the db failed in the middle, all these locks
would naturally disappear.

(UNTESTED)

dim db as database, rs as recordset
set db = curentdb()
on error resume next
set rs = db.openrecordset ( "SELECT 1 FROM MYTABLE WHERE KEY=" &
me![key], ?????? )
select case
case 0 ' got it.
' continue.
case ??? ' oops, someone else got it.
msgbox "nope, someone else has this one!"
case else ' other (unexpected) error.
(whatever)
end select
on error goto 0

I have to go in a moment, so I might not see yor rply for several
hours.

HTH,
TC

.