[AccessD] Record locking bug

I think I have stumbled on a record locking bug.

For an Insurance company call center, I have built a system of events.
Events happen to a claim, the claim is received, it is pended (waiting for
action), opened, closed, phone calls made etc.  These events may or may not
cause a change in status.  The receive event causes a received status, the
Open event causes an Open status.  A Phone cause does not cause a status
change.  If the claim is in an open status then it remains in an open status
etc.

The managers can define the events that can occur, whether they cause a
status change etc.  Via a state machine, I look at the current status and
enable specific events.  Obviously if a claim is in the open status, then it
can have many different events occur, but it cannot have a received event
occur because it was already received and can only be received once.  If a
claim is denied, it can be appealed, but it cannot be closed, because it was
never opened.  

So... There are many tables.  There is a claim table ad a claim event table.
The claim event is where we log events happening to the claim.  It is in
this table specifically that I am noticing the locking issue.  When I
designed this db I ported existing data from thousands of claims.  The old
system didn't have an event table, it had a bunch of fields such as
openedDate1, closedDate1, openedDate2, closedDate2 etc.  Hmmmm...

Anyway, in order to port the data I did my best to figure out how these
fields mapped to events and created a set sequence of:

Received
Pended

Then Opened or denied depending on what was in these fields.  Thus each old
claim would have at least 3 or 4 or more events to indicate that it was
received, that it was opened, closed, possibly opened again, possibly closed
again etc.

OK... With that groundwork...

I have discovered that if I build a little query that pulls all the events
for claim 800:

EventID
ClaimID
SomeField

And another identical query pulling all events for claim 801 (actually about
10 claims on either side of the claim)...

It certainly appears that it is locking the block that the event records are
built in.  When I start an edit and don't save, other events with PKs close
to that event are also locked.

I have pulled just the table of events into a new db, and designed these
identical queries and I am seeing the same thing.  I am convinced it is a
bug.  I see it on my dev machine at the client (Win2K A2K), my dev machine
at home (Win2K, A2K or AXP), but not on another machine running OfficeXP at
the client.

Truly bizarre.

I cut most of the fields out of the record including the only memo, both to
drop the size of the test db as well as to see if any of these other fields
were "causing" the problems.  No help, same symptoms.

The current db is just that one table and the two queries and is absolutely
reproducible on my machine here at my home office, running Win2K A2K SP3 or
Axp 

I have been under the impression that Access no longer locks an entire
block, but will lock just the individual record.  This testing seems to
indicate that is not true.

I have this db available for anyone who would like to look at this.  My
users have complained of locking issues and I have been unable to explain
the goings on given my understanding that A2K and above locked just the
edited record.  Perhaps this is not always true?

John W. Colby 
www.ColbyConsulting.com