[AccessD] Intermediate Unbound Form Part 2.

The first function our Person class should be able to do is to save it's own
data.  The one real trick being that we need to determine if we are changing
an existing record or adding a new one.  That's pretty easy to accomplish
since we added the blNew module level variable in Part1.  So our Save
function should look like this:

Friend Function Save()
Dim rs As ADODB.Recordset
Dim strSQL As String
Set rs=new ADODB.Recordset
If blNew Then
    rs.Open "tblPeople", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
    rs.AddNew
Else
    strSQL = "SELECT PersonID, FirstName, LastName FROM tblPeople WHERE
PersonID=" & ID
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
    If rs.EOF = False Then rs.MoveFirst
End If
rs.Fields("FirstName").Value = FirstName
rs.Fields("LastName").Value = LastName
rs.Update
If blNew Then
    intID = rs.Fields("PersonID").Value
    blNew = False
End If
rs.Close
Set rs = Nothing
End Function

We should also create a delete function, so let's add this function to the
Person Class:

Friend Function Delete()
Dim strSQL As String
strSQL = "DELETE * FROM tblPeople WHERE PersonID=" & ID
If blNew = False Then CurrentProject.Connection.Execute strSQL
End Function

Note the Friend statement before function.  This makes our functions visible
to our project, but not outside of it.

Now let's create interface controls to use our new functionality.  First,
let's set the text boxes on our frmPeople to update the CurrentPerson object
when their data is changed.  So here are the AfterUpdate events for those
three controls:

Private Sub txtFirstName_AfterUpdate()
CurrentPerson.FirstName = Me.txtFirstName
DisplayPerson
End Sub

Private Sub txtFullName_AfterUpdate()
CurrentPerson.FullName = Me.txtFullName
DisplayPerson
End Sub

Private Sub txtLastName_AfterUpdate()
CurrentPerson.LastName = Me.txtLastName
DisplayPerson
End Sub

Now let's add three buttons to our form.  cmdSave, cmdNew and cmdDelete.
Here are the OnClick events for cmdNew and cmdDelete:

Private Sub cmdDelete_Click()
Dim intPos As Long
Select Case Me.SortOrder.Value
    Case 1
        intPos = CurrentPerson.FirstSortOrder
    Case 2
        intPos = CurrentPerson.LastSortOrder
End Select
CurrentPerson.Delete
Set CurrentPerson = Nothing
Set PeopleClass = Nothing
Set PeopleClass = New People
If intPos > PeopleClass.PeopleCount Then intPos = PeopleClass.PeopleCount
Select Case Me.SortOrder.Value
    Case 1
        Set CurrentPerson = PeopleClass.PersonByFirstName(intPos)
    Case 2
        Set CurrentPerson = PeopleClass.PersonByLastName(intPos)
End Select
DisplayPerson
End Sub

Private Sub cmdNew_Click()
Set CurrentPerson = New Person
DisplayPerson
Me.txtFirstName.SetFocus
Me.cmdNew.Enabled = False
End Sub

For cmdSave, we need to make an additional capability for the People class.
We can get Person objects by their sort order (first or last name), but we
don't have a method of getting a person based on their ID.  If we add or
change someone, we want to be able to 'resort' them into the current list.
However, we want to be able to stay on the same person, and the logical way
to do that is to be able to retrieve that person based on their ID.  So add
this property to the People class:

Property Get PersonByID(intID As Long) As Person
Set PersonByID = PeopleByFirst("ID:" & intID)
End Property

Now that we have that in place, let's put the following for the click event
of cmdSave:

Private Sub cmdSave_Click()
Dim intID As Long
CurrentPerson.Save
intID = CurrentPerson.ID
Me.cmdNew.Enabled = True
Set PeopleClass = Nothing
Set PeopleClass = New People
Set CurrentPerson = PeopleClass.PersonByID(intID)
DisplayPerson
End Sub

I will sum up this lesson in the next post.  For those following along, I
have the Basic and Intermediate databases available for download at
http://www.marlow.com/UnboundDemo.zip (I will probably not keep that up
forever, but it'll be there for the next few weeks.)

Drew