John W. Colby
jwcolby at colbyconsulting.com
Fri Mar 12 23:44:25 CST 2004
The example database and a word document of this email is on my site -
www.colbyconsulting.com Click C2DbFW3G, then the hotlink to
C2DbFW3G-DemoCtlClassV3.zip.
Eventually all of these emails will be cleaned up by DatabaseAdvisors and
published in Many-to-Many.
The ultimate purpose of a framework is to give you clearly defined places to
go to place properties and behaviors for objects that should be available
for future projects. Controls are a prime example of this since they have
no built-in class and yet we want to extend their functionality. Thus we
build classes for each control type so that we can extend the functionality
of that control.
To this end in this discussion we are going to add some new functionality to
the combo class we defined. The new functionality will be dependent object
re-query.
Dependent object re-query means that we will create a method of selecting
objects (classes) that filter their data based on the currently selected
data in the control – in this case a combo. As the user selects new data in
the combo, any dependent objects need to be automatically re-queried so that
they display filtered data that depends on this combo.
This behavior is generally useful and will demonstrate how we expand a
framework to add things that we want our framework to do.
Dependent Objects
Dependent objects are a common occurrence in Access applications. As an
example a form has two combos on it, cboCompany and cboEmployees. As the
user selects cboCompany, cboEmployees needs to be filtered to only display
the employees for the company just selected. Or perhaps a company is
selected and only Addresses for that company are displayed in cboAddress.
Dependent objects are usually implemented by referring to the filtering
object in a field of the query which pulls data for the dependent object.
In other words, cboAddress will have something that looks like “Like
form!frmMyForm!cboCompany” in the criteria of the query for cboAddress or
cboEmployees. cboAddress is filtered by or dependent on cboCompany.
In order to implement this functionality in our framework we could simply
add a dependent object collection directly inside of the control class, then
add methods to each control class to add objects to that collection, remove
them again when the control class closes, and of course a re-query method to
iterate through all of the objects in the collection, calling the re-query
method of every object. Notice that this implies that every control that
can be dependent on another control must have a public re-query method in
its class.
The dependent object code
The code for this would look something like:
In the header of each control class (stripped of all error handling for easy
reading):
Private mcolDepObj As Collection
Collection initialization code in each control class’ Initialize event
Private Sub Class_Initialize()
Set mcolDepObj = New Collection
End Sub
Cleanup code in each control classes Term() function
Public Sub Term()
Set mcolDepObj = Nothing
End Sub
And finally public functions (methods) to handle the dependent object
specific stuff:
Private Function ColEmpty()
While mcolDepObj.Count > 0
mcolDepObj.Remove 1
Wend
End Function
'Requery every obj in the dependent object collection
Public Function Requery()
Dim Obj As Variant
For Each Obj In mcolDepObj
Obj.Requery
Next Obj
End Function
'Add multiple objects into the collection
Public Function DepObjs(ParamArray lDepObjsArr() As Variant)
Dim Obj As Variant
For Each Obj In lDepObjsArr
mcolDepObj.Add Obj, Obj.Name
Next Obj
End Function
'Add a single object
Public Function Add(item As Variant, key As Variant)
mcolDepObj.Add item, key
Requery
End Function
'Remove a single object
Public Function Remove(index As Variant)
mcolDepObj.Remove index
End Function
'get a count of the objects in the collection
Public Function Count()
Count = mcolDepObj.Count
End Function
'get one specific object back out of the collection
Public Function item(index As Variant)
item = mcolDepObj.item(index)
End Function
There’s nothing wrong with doing it that way but we are trying to learn to
use classes and another way we could implement this is to build a dependent
object class. Then we simply add a dependent object class to each control
class that needs one.
A huge advantage to using a dedicated class for this is that now we put all
of that exact same code in one place, in one class, then simply dim a
clsDepObj wherever we need it and call the class’ methods. We don’t have to
go add this stuff to each and every control class that may have dependent
objects.
Each control class’ code
Instead of all that code above in each control class, we now have:
In each control class’ header (stripped of all error handling for easy
reading):
Private mclsDepObj As clsDepObj
Initialization in each control class’ Initialize event.
Private Sub Class_Initialize()
Set mclsDepObj = New clsDepObj
End Sub
Cleanup in each control class’ Term()
Public Sub Term()
mclsDepObj.Term
Set mclsDepObj = Nothing
End Sub
And a property get to return a handle to the clsDepObj class instance for
this control class’ instance
Public Property Get clsDepObj() As clsDepObj
Set clsDepObj = mclsDepObj
End Property
You have to admit adding a few lines of code to each control class is way
cleaner and easier than adding all of that dependent object code to each
control class. Not to mention if we want to add some other dependent object
functionality to the framework we just do it in one place instead in every
control class.
'Requery the control and then requery all dependent objects
Public Function Requery()
mcbo.Requery
mclsDepObj.Requery
End Function
One thing that we have to remember is to add a re-query method to every
control class that will have a dependent object class. Obviously if this
class’ control has a data source then we need to re-query this control as
well as calling the re-query method of the dependent object class.
And finally, when the combo AfterUpdate fires we want to re-query all the
objects in our new class’ dependent object collection. We do that by
calling the mclsDepObj’s re-query method. Withevents! You gotta love them!
Private Sub mcbo_AfterUpdate()
mclsDepObj.Requery
End Sub
So now that we have this wonderful new class and functionality, how do we
tell the framework to use it? The answer to that lies in the form class’
initialization which occurs in the form’s Open event.
The form’s built-in class
I have modified frmPeopleV3 to add two combos in the form header section.
cboCompany and cboEmployee respectively contain data for companies and
employees of those companies. Thus the form’s code now looks like:
Option Compare Database
Option Explicit
Our custom form class dclsFrm
Public fdclsFrm As dclsFrm
And the form’s Open event.
Private Sub Form_Open(Cancel As Integer)
Set fdclsFrm = New dclsFrm
fdclsFrm.Init Me, Me
With fdclsFrm.colClasses
.item("cboCompany").clsDepObj.Add .item("cboEmployee"),
cboEmployee.Name
.item("cboCompany").clsDepObj.Add fdclsFrm, Me.Name
.item("cboCompany").Requery
End With
End Sub
I know this looks very complicated but we will take it one step at a time
and show you what we are doing. The set statement and init look very much
like before. We are passing in a reference to the form’s built in class
(Me) and a reference to the form itself (Me). Unfortunately in the case of
the form, Me refers both to the class as well as the physical form so that
can be a little odd at times.
Set fdclsFrm = New dclsFrm
fdclsFrm.Init Me, Me
Next we use a with statement to do a partial resolution of dclsFrm to speed
up getting at things.
With fdclsFrm.colClasses
dclsFrm has a property fdclsFrm.colClasses that returns a pointer to the
classes collection. Using that we can now refer to that collection’s
properties and methods. We use the .item() method to return something from
the collection.
.item("cboCompany").clsDepObj.Add .item("cboEmployee"), cboEmployee.Name
Remember that in dclsFrm we had a control scanner that found all the
controls on the form, built a class for each one, and placed a pointer to
each control’s class instance in colClasses. Remember also that we used the
control’s name as the key or index into the collection. Thus
.item("cboCompany") tells the class to return the item in colClasses that
was named “cboCompany, in other words the control class for the control
cboCompany.
Thus:
.item("cboCompany")
is a pointer to a combo class and is exactly equivalent to the more verbose:
dim ldclsCbo as dclsCbo
set dclsCbo = .item(“cboCompany”)
Since we are dealing with a pointer to a combo class we can now call any
method or property of that class which is what we do with:
.item("cboCompany").clsDepObj.Add
We are saying “for the combo company class, call the clsDepObject method
which returns a pointer to the dependent object class, and then call that
classes add method.
Whew! Just remember that the “period” denotes a method or property of the
class, so you just end up referencing classes with properties that return
classes with properties that return classes where you finally use a method
of that class.
fdclsFrm.colClasses.item(“class name”).clsDepObj.Add
By the way, you can set a break point on that line of code and step through
the code watching the methods of the various classes being called, returning
pointers to the next object, which steps into a method of that and returns a
pointer to the next object etc. until you finally “drill down” to the final
.Add method.
This stuff is FUN!
OK, back to the big picture.
With fdclsFrm.colClasses
.item("cboCompany").clsDepObj.Add .item("cboEmployee"),
cboEmployee.Name
.item("cboCompany").clsDepObj.Add fdclsFrm, Me.Name
.item("cboCompany").Requery
End With
We have now told the dclsFrm to add something to the clsDepObj. The
“something” is the class for the cboEmployee.
.item("cboCompany").clsDepObj.Add .item("cboEmployee"),
cboEmployee.Name
Likewise I have made the form itself dependent on the cboCompany so we also
have to tell the class for cboEmployee that dclsFrm is a dependent object.
.item("cboCompany").clsDepObj.Add fdclsFrm, Me.Name
And finally, we have to re-query cboCompany so that it requeries all of its
dependent objects.
.item("cboCompany").Requery
And that, as they say, is that.
Reference the form’s colClasses, pulling out the class for cboCompany.
Using the clsDepObj method of that class to pull the clsDepObj, use that
class’ Add method to add an object to the dependent object collection. The
object added is the class for cboEmployee and the class for the form itself,
fdclsFrm. Once we have added these two items to our dependent object class,
re-query the whole structure.
I know quite well that this stuff can make your eyes cross when you first
look at it but believe me it will become second nature once you have worked
with classes, methods and properties, and collections for awhile.
Summary
By adding a new, rather small class with a single collection variable and a
handful of public methods, we have built a method of manipulating dependent
objects. Once the class exists, it is a simple matter to add a few lines of
code to the class of each control that can have dependent objects and by
doing so add dependent object handling to any class that needs it. Probably
the biggest stretch was decoding the rather obtuse code in the form’s Open
method that “programs” the specific class that has dependent objects,
dclsCbo for cboEmployee, thus telling it to place pointers to two objects in
its dependent object handling system. Those two objects are the class for
cboEmployee and the class for the form itself.
The end result is that when the form initially opens it displays no records
because there is no company selected and the form is filtered by or
dependent on cboEmployee. However once we select an employee, the combo now
has something that can be used to filter the form and the cboEmployee.
Since cboCompany calls its clsDepObj’s re-query, the form’s class requeries
the form, and cboEmployee’s class requeries cboEmployee. By the way, if
cboEmployee had been programmed with dependent objects of its own,
cboEmployee’s class would have requeried all of its dependent objects and
the process just ripples down the chain automagically.
Classes are powerful tools that allow us to encapsulate functionality into a
single location, hold all of the variables and code needed to implement
necessary behaviors, and provide a single place to go to add new related
behaviors if necessary. You can pull this dependent object class into your
own framework and with relatively little effort add dependent object
processing to your own combos and other controls. Dependent object
processing is a trivial application, but imagine if the class had dozens of
properties and methods! This encapsulation and portability becomes a prime
reason to know how to use classes.
John W. Colby
www.ColbyConsulting.com