[AccessD] Basic Unbound Form with Classes and Collections Part 1.

Thanks Drew, this is just what I was looking for.

You are a classy guy... Pun intended.

Bill
----- Original Message ----- 
From: <DWUTKA at marlow.com>
To: <accessd at databaseadvisors.com>
Sent: Friday, June 09, 2006 9:32 AM
Subject: [AccessD] Basic Unbound Form with Classes and Collections Part 1.


I will do this demo with Access 2000 (97 will be a little different).

Let's start with a blank database.  We'll add a table called tblPeople with
the following fields:

PersonID (AutoNumber) (Primary Key)
FirstName (Text)
LastName (Text)

Now we want to create a class module.  (Again, in 97 this is a little
different).  The best way to do this (without creating a blank module that
you'd have to remove) is to click Tools--> Macro  -->  Visual Basic Editor.
You are now in the Visual Basic Editor.  We need a Class Module, so click
Insert --> Class Module.  (You will note that Class modules and Basic
Modules have different icons.  There should be a tool bar with the Access
icon a basic module icon and your save icon.  That basic module one has a
drop down, which will also let you create a new class module. (To do this in
97, you have to start with a new basic module, which you can remove later,
but when you are in a code page in 97, that is when you will get the option
to create a new class module.)

This new module will be called Class1 by default.  Let's change the name (in
the properties pane) to Person.  Now, in the code page of our Person Class,
let's add the necessary properties.  Our table has three fields, so we'll
create three easy properties, and a fourth which will be derived.

So, in the code page type the following under the 'Option Compare Database'
line:

Public ID As Long
Public FirstName As String
Public LastName As String

We also want to create a 'Full name' property, which is going to be a
combination of the first and last names.  For now we are going to leave this
property as read only.  Put the following procedure in your class module:

Property Get FullName() As String
FullName = FirstName & " " & LastName
End Property

See what we have done?  We now have 4 properties, ID, FirstName, LastName,
and FullName.  The first three can be read and written too, the last one is
read only.  For a simple value only property using the Public declaration in
a class module allows a simple variable to be a property all on it's own.
However, you can create more complex properties using the Property Get and
Property Let statements.  We used the Get statement to provide the FullName
property, but if we wanted to make that property writable we would use a let
statement.  I'll do that in the intermediate example.

We now have a class to represent a person in our database.  Now let's create
a class to represent all of the people in the database.  Create a new class,
and name it People. (Follow the same steps we used to create the Person
class).

We are going to create to collections of Persons in our People class. One
will be sorted by first name, one by last name.  Here's how we are going to
do it, first we are going to dimension the collections.  Below the Option
Compare Database, put the following:

Dim PeopleByFirst As Collection
Dim PeopleByLast As Collection

Now we need to create a procedure to fill these collections.  Create the
following procedure:

Private Sub GetPeople()
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim ps As Person
Set PeopleByFirst = New Collection
Set PeopleByLast = New Collection
Set rs = New ADODB.Recordset
strSQL = "SELECT PersonID, FirstName, LastName FROM tblPeople ORDER BY
FirstName"
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
If rs.EOF = False Then rs.MoveFirst
Do Until rs.EOF = True
    Set ps = New Person
    ps.ID = rs.Fields(0).Value
    ps.FirstName = rs.Fields(1).Value
    ps.LastName = rs.Fields(2).Value
    PeopleByFirst.Add ps, "ID:" & ps.ID
    Set ps = Nothing
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
strSQL = "SELECT PersonID, LastName FROM tblPeople ORDER BY LastName"
If rs.EOF = False Then rs.MoveFirst
Do Until rs.EOF = True
    Set ps = PeopleByFirst("ID:" & rs.Fields(0).Value)
    PeopleByLast.Add ps, "ID:" & ps.ID
    Set ps = Nothing
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

A few things to note in this procedure.  First, we initialize the
collections we have dimensioned in the class modules header.  We also pull
the recordset up the first time and let ADO/Jet sort the initial list by
FirstName.  We then add all of the people to the collection in the order
provided by the recordset.  Note that when we add it, we give it the 'ps'
object as what we are adding, but the second argument is a custom ID.
Collections can be recalled by the order in the collection (ie,
PeopleByFirst(1) would give you the first person in the collection), or by
the custom (and unique) id that you give it, which we do in the second loop
(PeopleByLast("ID:" & rs.fields(0).value)).  In the second loop, we are
again letting ADO/Jet sort things for us, but we aren't creating any more
Person objects.  A Collection is really a set of pointers to objects, so you
can have 1 object in several different collections.  The advantage to this
is that if you change an object that is in multiple collections, referencing
it from any of those collections will show the change.  ie, if we Change Bob
Smith to Bobby Smith, both collections (sorted by first and last name) will
reference the new 'Bobby Smith' object.  A point is simply a long integer,
so if we were dealing with larger class objects, we wouldn't suffer from
having duplicate objects in memory.

Now we need to set it so this class initializes itself.  In the top left of
the code page, you should see a drop down that currently says (General),
this is your object drop down (it lists the objects this code page/class
sees).  Select the only other option in there (right now) 'Class'.  It will
change the right side dropdown to 'Initialize', which is one of two events
every class has.  (The other event is terminate).  You should now have a
'Private Sub Class_Initialize()' procedure.  we just want it to run our
GetPeople routine, so type that in there, it should look like this:

Private Sub Class_Initialize()
GetPeople
End Sub

I will continue in another email (to keep the size of this down a bit)

Drew

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com