[AccessD] Intermediate Unbound Form Part 1.

I think it's a matter of taste/style.  I'm intentionally going back and
forth a bit.  If I were building a project like this from the get go, I
would have a fully functional 'Person' class before I created a 'People'
class.  But I wanted to start with some basic stuff before moving to more
advanced stuff.  I didn't get a chance to work on this stuff over the
weekend, so I'll be finishing the intermediate lesson this morning
(hopefully), and moving to the advanced one.  I'm going to get into custom
events in the advanced one.

As far as passing off the recordset object, I don't really see the advantage
of that.  If I built this my normal way, the Person class would be self
contained, truly representing a person in the database.  It could pull up
it's own information and do whatever necessary (add, edit, delete, etc.).
However, the People class would be similar to what I'm demoing.  Why pass
the recordset object back and forth when I simply want the data put into
several instances of the Person class?  It's just as easy to put the data
through.....

Drew

-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Saturday, June 10, 2006 2:10 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Intermediate Unbound Form Part 1.


Drew,

One thing I would suggest is to have the people class pull the recordset of
all people that you are going to process, but then move through the
recordset, passing the RECORDSET OBJECT off to the Person class, and allow
that class populate itself, read / write / edit.  I recommend that for the
simple reason that as you add properties to the person class, you add
functionality to do the read/write/edit of the person record right in the
person class itself rather than back and forth between the people and person
class.  Keep the processing as close to home as possible.

Kind of the best of both worlds.

JWC 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Friday, June 09, 2006 5:17 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Intermediate Unbound Form Part 1.

Our beginning demo allowed us to view data in an unbound form.  Unbound
forms can allow us to handle complex display methods a little easier then
bound forms.  (Not to start a bound/unbound war again, but simply stated,
bound forms are great for using standard methods.  A simple bound form can
be created in a fraction of the time as a similar unbound form, however,
there are some things which can be a lot easier to handle when unbound.).

To start, we want to allow a person to be created, edited, and deleted.  The
best place to do this would be in the Person class itself.  It already has
all of the properties associated with a Person, we should keep with the same
mindset about what we can do with a Person (and the data associated with
them).  But our simple class needs a few modifications.  To begin with, the
ID is set as a simple property (Public ID as long).  We need to change that.
The ID field is the unique identifier, so getting the value is going to be a
straight forward Property Get, however, when that value is set, we may want
the class to fill itself out.  We may not, too.  So we are going to create a
StorageOnly property.  A simple boolean variable that we will set to False
when the class initializes.  In our People class, we will change the
GetPeople procedure to set that property to True, before it sets the ID
value.

Let me explain that.  We are going to make the Person more independent.  In
the beginning example, the class had properties, but it couldn't do anything
on it's own, if initialized by itself, all of it's values are blank.  We are
going to change that, so we could bring up a particular user simply by
setting the id value.  However, the People class fills it's collections with
the Person class, and it has to set the ID property.  We could let each
instance of Person get it's own data based on ID, however, we would be
opening and closing a lot of recordset to do that.  It's faster and more
efficient to let the 'group' class pull up the whole recordset and just fill
in the data.  To do this, we are using the StorageOnly property as a switch
to override the independence of the Person class.

We also are going to create a Save routine, so let's plan ahead a bit.  We
will want to know if this is an existing record or a new record.  This is
can be accomplished in several ways.  We could initialize the intID variable
with a value of 0.  In this particular case, it would work pretty well, the
0 won't show up as a value in an Autonumber field for quite sometime.
However, the more fool proof method isn't much more difficult.  We will
create another module level boolean variable blNew.  When the class is
initialized, we'll set it to True, and when the ID is set (regardless of
StorageOnly) we will set it to False (because it is then an existing
Person).

Our new Person Class should look like this:

Public FirstName As String
Public LastName As String
Public FirstSortOrder As Long
Public LastSortOrder As Long
Public StorageOnly As Boolean
Dim intID As Long
dim blNew as Boolean

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

Property Get ID() As Long
ID = intID
End Property

Property Let ID(intEnter As Long)
intID = intEnter
blNew=False
If Not StorageOnly Then
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT FirstName, LastName FROM tblPeople WHERE PersonID=" &
ID
    Set rs = New ADODB.Recordset
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF = False Then
        rs.MoveFirst
        FirstName = rs.Fields(0).Value
        LastName = rs.Fields(1).Value
    End If
    rs.Close
    Set rs = Nothing
End If
End Property

Private Sub Class_Initialize()
StorageOnly = False
blNew=True
End Sub

Notice the changes we made?  We added the StorageOnly property in the
declarations.  We also set that property when the class is initialized.  We
then created get and let statements to allow the ID property to be
read/write.  When we set the ID property now, if the StorageOnly is still
false, it gets the first and last name properties based on the ID set.

Now we better go change the GetPeople function in the People class, so it
set's the StorageOnly property before setting the id.

It's one simple line, it's the new third line in the code below:

Do Until rs.EOF = True
    Set ps = New Person
    ps.StorageOnly = True
    ps.ID = rs.Fields(0).Value
    ps.FirstName = rs.Fields(1).Value
    ps.LastName = rs.Fields(2).Value
    ps.FirstSortOrder = i
    PeopleByFirst.Add ps, "ID:" & ps.ID
    Set ps = Nothing
    rs.MoveNext
    i = i + 1
Loop

We only create a new Person instance in the first loop (because the second
loop is using existing instances), so we just need to set that value right
after we create the new instance.

Let's go and create a let statement for our FullName property.  We'll do so
by adding this to the Person class:

Property Let FullName(strEnter As String) Dim strArray() As String strArray
= Split(strEnter, " ") FirstName = strArray(0) LastName = strArray(1) End
Property

Yes, I know, it is quick and dirty, but this is a demo ya know! ;)

We will create the functions of our Person class in Part 2.

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


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