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

Okay, we now have our People and Person classes built.  (I will post a
followup email to this explaining some of my methods and naming conventions
a bit, so don't get in a huff, you can name things whatever you want, this
is just a demo tutorial.).

The basic example isn't going to add or change records, I'll do that in the
Intermediate example.  So let's open tblPeople, and create some records.  To
keep everyone on the same page, let's put in the following names:

Susan Harkins
John Colby
Gustav Brock
Darren Dick
Drew Wutka
Jim Hale
Paul Hartland
Charlotte Foust
Rocky Smolin

Now let's create an unbound form to display our people table.  Go to the
forms tab, and click New, then Design View.  We now have a blank form in
front of us.  Let's put in a few controls.  Let's add a label called
'lblRecordDisplayed'.  Let's add a Frame/Option group, and call it
'SortOrder'.  Put two radio buttons in there, name them what you want, but
have the firstname have an option value of 1, and the last name have an
option value of 2.  Add three text boxes 'txtFirstName', 'txtLastName', and
'txtFullName'.  Add two command buttons 'cmdNextRecord' and
'cmdPreviousRecord'.  What you label and where you place these controls is
up to you.

Now let's take a step back.  We've painted ourselves in a corner, somewhat,
by creating a sort order.  There are many ways to handle this, but let's go
back to the Person Class and add:

Public FirstSortOrder as Long
Public SecondSortOrder as Long	

And let's go back to the People Class and change the GetPeople sub to this:

Private Sub GetPeople()
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim ps As Person
Dim i As Long
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
i = 1
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
    ps.FirstSortOrder = i
    PeopleByFirst.Add ps, "ID:" & ps.ID
    Set ps = Nothing
    rs.MoveNext
    i = i + 1
Loop
rs.Close
Set rs = Nothing
Set rs = New ADODB.Recordset
strSQL = "SELECT PersonID, LastName FROM tblPeople ORDER BY LastName"
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
If rs.EOF = False Then rs.MoveFirst
i = 1
Do Until rs.EOF = True
    Set ps = PeopleByFirst("ID:" & rs.Fields(0).Value)
    ps.LastSortOrder = i
    PeopleByLast.Add ps, "ID:" & ps.ID
    Set ps = Nothing
    rs.MoveNext
    i = i + 1
Loop
rs.Close
Set rs = Nothing
End Sub

'Note, there was a bug in here, I didn't reinitialize and open the recordset
in the previous version.

Each Person class now knows the order they fall into in both First and Last
name sorting.  We also need to create a process to get the Person object out
of the people class and we need to know how many people there are, so add
the following routines to the People class:

Property Get PersonByFirstName(intOrder As Long) As Person
Set PersonByFirstName = PeopleByFirst(intOrder)
End Property

Property Get PersonByLastName(intOrder As Long) As Person
Set PersonByLastName = PeopleByLast(intOrder)
End Property

Property Get PeopleCount() As Long
PeopleCount = PeopleByFirst.Count
End Property

Now let's put this into play on our form.  Let's save our form as frmPeople,
then go to the form's property page, then the Events tab, and use the code
builder on the On Load event.  First we need to dimension a variable for our
collection of people, and we need to know who the current person is, so put
this in the declarations:

Dim PeopleClass As People
Dim CurrentPerson As Person

Now in our Private Sub Form_Load() event, let's put 'Set PeopleClass=New
People' and we need to set the first user, should look like this:

Private Sub Form_Load()
Set PeopleClass = New People
Set CurrentPerson = PeopleClass.PersonByFirstName(1)
DisplayPerson
End Sub

Now, we need to build a subroutine to display the user.  We are going to
build a routine which will display the information no matter what the order
is.  Here is our function (which is run on the Form_Load() event)

Private Sub DisplayPerson()
Me.txtFirstName = CurrentPerson.FirstName
Me.txtLastName = CurrentPerson.LastName
Me.txtFullName = CurrentPerson.FullName
Select Case Me.SortOrder.Value
    Case 1 'Sorted by first name
        Me.lblRecordDisplayed.Caption = "Record " &
CurrentPerson.FirstSortOrder & _
        " of " & PeopleClass.PeopleCount
        If CurrentPerson.FirstSortOrder = 1 Then
            Me.cmdNextRecord.SetFocus
            Me.cmdPreviousRecord.Enabled = False
        Else
            Me.cmdPreviousRecord.Enabled = True
        End If
        If CurrentPerson.FirstSortOrder = PeopleClass.PeopleCount Then
            Me.cmdPreviousRecord.SetFocus
            Me.cmdNextRecord.Enabled = False
        Else
            Me.cmdNextRecord.Enabled = True
        End If
    Case 2 'sorted by last name
        Me.lblRecordDisplayed.Caption = "Record " &
CurrentPerson.LastSortOrder & _
        " of " & PeopleClass.PeopleCount
        If CurrentPerson.LastSortOrder = 1 Then
            Me.cmdNextRecord.SetFocus
            Me.cmdPreviousRecord.Enabled = False
        Else
            Me.cmdPreviousRecord.Enabled = True
        End If
        If CurrentPerson.LastSortOrder = PeopleClass.PeopleCount Then
            Me.cmdPreviousRecord.SetFocus
            Me.cmdNextRecord.Enabled = False
        Else
            Me.cmdNextRecord.Enabled = True
        End If
End Select
End Sub

In the next email, we'll finish the job.

Drew