[AccessD] Class modules example

At one client I have built a rather elaborate system that tracks events that
happen to an insurance claim.  As events happen they are recorded in an
event table that is child to insurance claim.  The events can cause status
changes, and events subsequent to any event that causes a status change
inherits the "current" status if it does not cause a status change.  Thus
the last event always displays the current status.

In each event record there are pointers to the immediate previous event, the
event that caused the current status, and the event that caused the previous
status.  I do that for reporting purposes, so that we can determine (with
queries) how long claims are in a given status, when the status changed etc.

Of course this means that as a new event is created these fields have to be
populated by code that looks up these previous events and fills in the FKs.
Furthermore if an event is deleted, these pointers will need adjusting.

Additionally, the users need the current event type / status displayed out
in the main claim form.  For speed reasons I have, with malice and
forethought, denormalized the db and placed a pointer to the last event in
the main claim record.  Likewise, if the LAST event is deleted, then this
pointer needs to be adjusted.  I adjust this pointer directly in the record
itself rather than by setting a value in a form simply so that the code that
create events aren't tied to a form being open.

Which brings up an interesting phenomenon - If the claim form IS OPEN, and
the field in the current claim that tracks the current event is modified,
any attempt to edit the current manually through the form will display a
message "another user has modified this claim - your changes will be lost".
The message is displayed as the user is entering the change in the very
first field being changed, and the change is discarded by Access, requiring
the user to start over after clicking the OK button to the message being
displayed.

In order to work around this and still use the encapsulation provided by the
classes building the events and updating the claim record, I am using
RaiseEvent to broadcast a message that the claim record has been modified,
at the time that the claim is updated.

The claim form listens for the message and if it sees a "claim modified"
message, immediately requeries the form.  Understand that the class cannot
update the claim if that claim is currently being modified by any user,
which in itself can cause a "multi-user" issue.

I have designed a "message" class (available on my site BTW) which allows
message class users to broadcast / receive messages.  

The event class which modified the claim gets a pointer to a global message
class.  It uses this pointer to send messages on that message channel.  

The claim form gets a pointer to the same message class.  In the claim
form's class module I set up an event handler to sink the message classes'
message event.  Thus the claim form now receives any events broadcast on
that message channel.  It watches the subject, and if the subject is "claim
record change", it looks in the message parameter and checks the claim ID
against the claim the form is currently displaying.  If the claim being
displayed if the claim form is the same as the claim the event class just
modified, then the claim form has to requery so that the user won't get
these annoying messages.

It seems like Access should just requery the data by itself if it senses
that the data has changed, and allow the user to continue modifying the data
after the requery.  Unfortunately that does not seem to be the case.

There are of course other ways to handle this problem.  

However, using a message class - which one class module uses to broadcast
messages, and another class module uses to receive messages - allows me to
encapsulate and isolate the various processes.  The event class can update
events without worrying about whether the claim form is open and trying to
cause it to requery somehow.  It can simply broadcast a message "hey, I
modified claim number XXX".  ANY form that might be affected can listen for
these messages and if it sees a message saying that the claim it is
currently displaying was modified, it can requery itself.

The event class logs any failed attempts to update the claim record (some
user was actively editing that claim at that moment) and another process
performs those updates later.

John W. Colby