| Carl Rapson 2005-05-19, 11:24 am |
| I have a multi-user database (Access 2002) that involves assigning a number
to new records (sort of like an invoice number). I'm maintaining the last
assigned number in a separate table, and I generate a new number by grabbing
the value, incrementing it by one, and updating that table with the new
value. I could also generate the next number by grabbing the Max value from
the data table and incrementing that, but I think the question discussed
below applies to whichever method I use.
I realize that there's probably little chance that two users could try to
get the next number at exactly the same time, but I would like to handle
that situation just in case it does occur. I've been trying to find a way to
lock the record while I increment the number, but from what I can see the
record isn't being locked. Here are my settings:
Tools|Options|Advanc
ed: Default record locking = Edited record
Open databases using record-level
locking = Checked
Recordset: type = dbOpenDynaset (data tables are linked)
options = dbSeeChanges
lockedits = dbPessimistic
When I open the recordset, I immediately call the .Edit method to
(hopefully) lock the record. At this point, and before I call the .Update
method, I open another form that opens an identical recordset and also calls
the .Edit method. I expected the second recordset to give me an error, but
it opens the record with no apparent problems. So it appears that I've got
two recordsets open in Edit mode on the same record.
How can I lock the record so that any subsequent attempts to open the same
record (before the .Update method is called) will return an error that I can
trap and deal with?
Thanks for any assistance,
Carl Rapson
|