[AccessD] DependentObjects class

John W. Colby jwcolby at colbyconsulting.com
Mon Mar 15 20:50:00 CST 2004


>On a given form I have N checkboxes or option buttons or whatever. Beside
these (some subset of all the controls), I have a DateUpdated field --
classic case of Dependent Object. But I want more than that. I want to write
to a table ValuesChanged, automatically (i.e. class behavior), sending the
table name, the field name, the userID and the dateTime. And have this
happen as a simple result of changing said control.

As it happens I am working on this exact functionality.  However... in the
end it didn't make sense to place this functionality in each control class
but rather to write a class that does this.  I started out adding a couple
of properties to each control class and going down that road.  It turns out
as things developed that having a set of tables worked better than a single
table.  Just my take of course but I normalized the process, tblTableName,
tblField, tblTransaction etc.  Once I got into the analysis of the problem
it seemed I wanted "transactional" information, i.e. workstation,
transaction date, form, control, things like that that I did not want to
have to put in every change record.

Things are never simple and in the simplest case a single table design was
enough, but as soon as you got to real life with forms that displayed (and
allowed edits to) 2 or more tables then the single table design just became
horribly inefficient.

So this is a good example of where perhaps the functionality does not belong
at the control level but rather in a "service class".  Instantiate the class
in the framework's form class, pass in the controls that you wish to
monitor, and the service class deals with it.  Remember that more than one
class can sink events for a given control.  It turns out however that you
really don't want to do this on control events because of the problems with
the user "backing out" or escaping out of changes, all of which cause no
events to fire!!!  Access is incredibly nasty when it comes to such things!
You really want to use a hand full of form events that call methods of the
service class requesting the service class to poll it's monitored controls
for old / new values (depending on the event).

By building a service class that monitors specific controls you can also do
things like "watch address controls" to cause a process to run only if the
address changes.  Just load an instance and tell it to watch "these 5
controls" and raise an event if any of them changed at the time the data was
stored back in the table.

As I said, I am looking at this exact problem.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Monday, March 15, 2004 3:04 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] DependentObjects class


Good answer(s). Now I want to push the envelope a bit, and ensure that
the framework can handle it....

On a given form I have N checkboxes or option buttons or whatever.
Beside these (some subset of all the controls), I have a DateUpdated
field -- classic case of Dependent Object. But I want more than that. I
want to write to a table ValuesChanged, automatically (i.e. class
behaviour), sending the table name, the field name, the userID and the
dateTime. And have this happen as a simple result of changing said
control.

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Friday, March 12, 2004 2:11 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] DependentObjects class


>Instead, they recommend creating a class that holds both controls as
attributes of the class. This may or may not work in VBA, of course.

And of course that can be done.  However since the framework exists (or
will!) and it has a combo class, why not allow any combo to know that
something else is dependent on it, and then when it changes go requery
anything that is dependent?

In addition, in many cases where I have seen this sort of "one combo
depends on another's value" thing done, it is IMO frequently the result
of bad design at the database level.

Yes, but you intentionally chose an example of normalization issues.
What about companies  / people / employees?  As you select a different
company in ComboA you want the "choice" of Employees in ComboB to
change?  What about Companies / payments, companies / invoices,
companies...

Dependent combos is SO common that it is a frequent "how do I" topic on
this very list.

>So in that case we're back to the custom class that embeds both combos.

Absolutely possible and in fact easy using classes and Withevents.  But
again, all I have to do is tell the class instance for ComboA that
ComboA "is dependent on it" and then whenever ComboA changes it
automatically requeries ComboB.  If comboC is dependent on ComboB, then
when ComboB is requeried by ComboA, ComboB looks in it's dependent
objects collection (or
class) and requeries all of ITs dependent objects.  This is a classic
case of a normal framework behavior replacing custom programming.

>But I do believe that designing classes that know specifics about other
classes (i.e. worst of all is that they must know the name of the other
class's instance) is fundamentally incorrect.

The class doesn't know anything except it has a collection of objects
that it needs to requery!!!  No clue at all what the objects are, just
that they are in its collection and it needs to call a requery method
for each one. In fact the objects can be other combos, other lists,
subforms, other open forms... anything in the application that is a
class and as a requery method.  The DEVELOPER knows that ComboB depends
on ComboA and in the form's Open event tells the framework (a specific
class instance).

Further combos aren't the only objects that can have dependent objects.
Basically anything (any control) that can be referenced by the SQL of
another control can have dependent objects.  A text box can be
referenced by a combo so it can have a dependent objects class.
Likewise a checkbox, a radio button, ANYTHING that can be referenced by
SQL in the recordset for a control or form.

Stick around for the discussion, we are about to add that functionality
to our "framework" (it isn't a framework yet!) so you can see what I am
talking about.

John W. Colby
www.ColbyConsulting.com






More information about the AccessD mailing list