Click to go home

This is the contact maintenance form

Option Explicit
Private rsContacts As Recordset

Private Sub cmdAdd_Click()
    ' Hide current form and call function to show new child form
    frmContacts.Hide
    frmMain.ShowChild "Add new Contact", rsContacts
End Sub

Private Sub cmdDelete_Click()
    On Error GoTo ErrorHandler
    ' Confirm deletion of record
    If MsgBox("Are you sure you want to delete this record from the database?", vbYesNo + vbExclamation + vbDefaultButton2, "Delete") = vbYes Then
    ' Check if there is only one record to avoid BOF/EOF errors
        If lstContacts.ListCount = 1 Then
            rsContacts.Delete
            rsContacts.UpdateBatch
            FillList
            Exit Sub
        End If
        ' Re-sort recordset to allow proper updating of ContactIDs
        rsContacts.Sort = "ContactId ASC"
        rsContacts.MoveFirst
        rsContacts.Find "ContactId = " & lstContacts.ItemData(lstContacts.ListIndex) & ""
        rsContacts.Delete
        rsContacts.MoveNext
        ' Subtract 1 from each ContactID after deleted record
        Do Until rsContacts.EOF
            rsContacts!ContactID = rsContacts!ContactID - 1
            rsContacts.MoveNext
        Loop
        rsContacts.MoveLast
        rsContacts.UpdateBatch
        FillList
    End If
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & Err.Description, , "Delete"
    FillList
End Sub

Private Sub cmdModify_Click()
    rsContacts.MoveFirst
    ' Move to record where contactID = itemdata of selected item
    rsContacts.Find "ContactId = " & lstContacts.ItemData(lstContacts.ListIndex) & ""
    frmContacts.Hide
    ' Call function to show child form, passing it selected item
    frmMain.ShowChild lstContacts.List(lstContacts.ListIndex), rsContacts
End Sub

Public Sub FillList()
    ' Open new recordset
    Set rsContacts = New Recordset
    With rsContacts
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .ActiveConnection = frmMain.cnDatabase
        .Open "SELECT * FROM Contacts"
    End With
    ' Call the sort by last name sub (default sort)
    optLast_Click
End Sub

Private Sub Form_Load()

    FillList

End Sub

Private Sub lstContacts_Click()
    ' Enable Delete and Modify buttons when list box is selected
    cmdDelete.Enabled = True
    cmdModify.Enabled = True
End Sub

Private Sub optCity_Click()
    lstContacts.Clear
    ' Exit sub if there are no records
    If rsContacts.BOF And rsContacts.EOF Then Exit Sub
    ' Sort by city
    rsContacts.Sort = "City ASC"
    ' Populate list box
    rsContacts.MoveFirst
    While Not rsContacts.EOF
        lstContacts.AddItem rsContacts!LastName & ", " & rsContacts!FirstName
        lstContacts.ItemData(lstContacts.NewIndex) = rsContacts!ContactID
        rsContacts.MoveNext
    Wend
    rsContacts.MoveFirst
    ' Disable Delete and Modify buttons (until an item is selected)
    cmdDelete.Enabled = False
    cmdModify.Enabled = False
End Sub

Private Sub optFirst_Click()
    lstContacts.Clear
    ' Exit sub if there are no records
    If rsContacts.BOF And rsContacts.EOF Then Exit Sub
    ' Sort by first name
    rsContacts.Sort = "FirstName ASC"
    ' Populate list box
    rsContacts.MoveFirst
    While Not rsContacts.EOF
        lstContacts.AddItem rsContacts!LastName & ", " & rsContacts!FirstName
        lstContacts.ItemData(lstContacts.NewIndex) = rsContacts!ContactID
        rsContacts.MoveNext
    Wend
    rsContacts.MoveFirst
    cmdDelete.Enabled = False
    cmdModify.Enabled = False
End Sub

Private Sub optLast_Click()
    lstContacts.Clear
    ' Disable Delete and Modify buttons if there are no records
    If rsContacts.BOF And rsContacts.EOF Then
        cmdDelete.Enabled = False
        cmdModify.Enabled = False
        Exit Sub
    End If
    ' Sort by last name
    rsContacts.Sort = "LastName ASC"
    ' Populate list box
    rsContacts.MoveFirst
    While Not rsContacts.EOF
        lstContacts.AddItem rsContacts!LastName & ", " & rsContacts!FirstName
        lstContacts.ItemData(lstContacts.NewIndex) = rsContacts!ContactID
        rsContacts.MoveNext
    Wend
    ' Select the last name option button by default
    optLast.Value = True
    rsContacts.MoveFirst
    cmdDelete.Enabled = False
    cmdModify.Enabled = False
End Sub

Private Sub optPhone_Click()
    lstContacts.Clear
    ' Exit sub if there are no records
    If rsContacts.BOF And rsContacts.EOF Then Exit Sub
    ' Sort by phone number
    rsContacts.Sort = "Phone ASC"
    ' Populate list box
    rsContacts.MoveFirst
    While Not rsContacts.EOF
        lstContacts.AddItem rsContacts!LastName & ", " & rsContacts!FirstName
        lstContacts.ItemData(lstContacts.NewIndex) = rsContacts!ContactID
        rsContacts.MoveNext
    Wend
    rsContacts.MoveFirst
    cmdDelete.Enabled = False
    cmdModify.Enabled = False
End Sub

Public Function ContactID() As String
    ' If there are no records, then return 1 as contactID
    If rsContacts.BOF And rsContacts.EOF Then
    ContactID = 1
    Exit Function
    End If
    ' Sort by ContactID and return the last ContactID + 1
    rsContacts.Sort = "ContactID ASC"
    rsContacts.MoveLast
    ContactID = (rsContacts!ContactID) + 1
End Function