[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