Option Explicit
 
Dim vID As String, vName As String, vAddress As String, vTelNum As String, vCity As String
 
Private Sub cmdCloseCancel_Click()
 
    If cmdCloseCancel.Caption = "&Close" Then
        End
    Else
        Call Default_Obj
    End If
 
End Sub
 
Private Sub cmdDelete_Click()
 
Dim vI As Integer
 
    If MsgBox("Are you sure you want to delete this entry?", vbQuestion + vbYesNo, "Confirm Delete") = vbYes Then
        With dtaDB
            .RecordSource = "SELECT * FROM DirectoryList WHERE ID='" & vID & "'"
            .Refresh
            With .Recordset
                If .BOF = False Then
                    .Delete
                    Call Default_Obj
                    Call Show_Existing("SELECT * FROM DirectoryList")
                Else
                    MsgBox "The record you want to delete doesn't exist.", vbExclamation, "Not Found"
                    Exit Sub
                End If
            End With
        End With
    Else
        Exit Sub
    End If
 
End Sub
 
Private Sub cmdEditUpdate_Click()
 
    If cmdEditUpdate.Caption = "&Edit" Then
       cmdEditUpdate.Caption = "&Update"
       fraSearch.Enabled = False
       fraInfo.Enabled = True
       FraBinfo.Enabled = True
       cmdCloseCancel.Caption = "&Cancel"
       vName = txtName
       vAddress = txtAddress
       vTelNum = txtTelNum
       vCity = txtCity
       txtName.SetFocus
       SendKeys "{HOME}+{END}"
 
    Else
 
        If Len(txtName) = 0 Then
            MsgBox "Full Name Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtName.SetFocus
            Exit Sub
        End If
 
        If Len(txtAddress) = 0 Then
            MsgBox " Address Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtAddress.SetFocus
            Exit Sub
        End If
 
        If Len(txtTelNum) = 0 Then
            MsgBox "Telephone # Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtTelNum.SetFocus
            Exit Sub
        End If
 
        If Len(txtCity) = 0 Then
            MsgBox "City Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtCity.SetFocus
            Exit Sub
 
        If vName = txtName And vAddress = txtAddress And vTelNum = txtTelNum And vCity = txtCity Then
            MsgBox "No changes was made in this record.", vbInformation, "Update Record"
                Call Default_Obj
            Exit Sub
        Else
            With dtaDB
                .RecordSource = "SELECT * FROM DirectoryList WHERE Name='" & txtName & "' AND Address='" & txtAddress & "' AND TelNum ='" & txtTelNum & "' AND City=' " & txtCity & " '"
                .Refresh
                With .Recordset
                    If .BOF = True Then
                        GoTo EditUpdate_Record
                    Else
                        MsgBox "This entry already exist.", vbExclamation, "Duplicate Entry"
                            txtName.SetFocus
                            SendKeys "{HOME}+{END}"
                        Exit Sub
                    End If
                End With
            End With
        End If
    End If
    Exit Sub
 
EditUpdate_Record:
    With dtaDB
        .RecordSource = "SELECT * FROM DirectoryList WHERE ID='" & vID & "'"
        .Refresh
        With .Recordset
            If .BOF = True Then
                MsgBox "The record you want to edit/update doesn't exist.", vbExclamation, "Not Found"
                Exit Sub
            Else
                .Edit
                !Name = txtName
                !TelNum = txtTelNum
                !Address = txtAddress
                !City = txtCity
                .Update
                Call Default_Obj
                Call Show_Existing("SELECT * FROM DirectoryList")
            End If
        End With
    End With
End If
 
End Sub
 
Private Sub cmdNewSave_Click()
 
    If cmdNewSave.Caption = "&New" Then
        Call Default_Obj
        cmdNewSave.Caption = "&Save"
        fraSearch.Enabled = False
        fraInfo.Enabled = True
        FraBinfo.Enabled = True
        cmdNewSave.Caption = "&Save"
        cmdCloseCancel.Caption = "&Cancel"
        txtName.SetFocus
    Else
        If Len(txtName) = 0 Then
            MsgBox "Full Name Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtName.SetFocus
            Exit Sub
        End If
 
        If Len(txtAddress) = 0 Then
            MsgBox "Home Address Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtAddress.SetFocus
            Exit Sub
        End If
 
        If Len(txtTelNum) = 0 Then
            MsgBox "Telephone # Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtTelNum.SetFocus
            Exit Sub
        End If
 
        If Len(txtCity) = 0 Then
            MsgBox "City Textbox contains no data.", vbExclamation, "Empty Textbox"
                txtCity.SetFocus
            Exit Sub
        End If
 
        With dtaDB
            .RecordSource = "SELECT * FROM DirectoryList WHERE Name='" & txtName & "' AND Address='" & txtAddress & "' AND TelNum ='" & txtTelNum & "' AND City='" & txtCity & "'"
            .Refresh
            With .Recordset
                If .BOF = True Then
                    .AddNew
                    !ID = Format(Date, "mmddyy") & Format(Time, "hhmmss")
                    !Name = txtName
                    !TelNum = txtTelNum
                    !Address = txtAddress
                    !City = txtCity
                    .Update
                    Call Show_Existing("SELECT * FROM DirectoryList")
                    MsgBox "New entry was successfully addedd.", vbInformation, "Record Saved"
                        Call Default_Obj
                    Exit Sub
                Else
                    MsgBox "This entry already exist.", vbExclamation, "Duplicate Entry"
                        txtName.SetFocus
                        SendKeys "{HOME}+{END}"
                    Exit Sub
                End If
            End With
        End With
    End If
 
End Sub
 
Private Sub Form_Load()
 
    dtaDB.DatabaseName = App.Path & "\TelDir.mdb"
    Call Default_Obj
    Call Show_Existing("SELECT * FROM DirectoryList")
 
End Sub
 
Private Sub Default_Obj()
 
    With Me
        .txtName = ""
        .txtAddress = ""
        .txtSearchKey = ""
        .txtTelNum = ""
        .txtCity = ""
        .cboSearchBy.Text = .cboSearchBy.List(0)
        .cmdCloseCancel.Caption = "&Close"
        .cmdDelete.Enabled = False
        .cmdEditUpdate.Caption = "&Edit"
        .cmdEditUpdate.Enabled = False
        .cmdNewSave.Caption = "&New"
        .fraInfo.Enabled = False
        .FraBinfo.Enabled = False
        .fraSearch.Enabled = True
 
    End With
 
End Sub
 
 
Private Sub Show_Existing(vSQL As String)
 
Dim vLst As ListItem
 Dim SubItems As String
    lstExisting.ListItems.Clear
    With dtaDB
        .RecordSource = vSQL
        .Refresh
        With .Recordset
            If .BOF = True Then Exit Sub
            .MoveFirst
            While Not .EOF
                Set vLst = lstExisting.ListItems.Add(, , !ID)
                vLst.SubItems(1) = !Name
                vLst.SubItems(2) = !Address
                vLst.SubItems(3) = !TelNum
                vLst.SubItems(4) = !City
                .MoveNext
            Wend
        End With
    End With
 
    lblCnt = "# of Records : " & lstExisting.ListItems.Count
    lstExisting.Refresh
    lstExisting.Refresh
 
End Sub
 
Private Sub lstExisting_Click()
 
Dim vI As Integer
 
    If lstExisting.ListItems.Count = 0 Then Exit Sub
 
    Call Default_Obj
 
    cmdEditUpdate.Enabled = True
    cmdDelete.Enabled = True
 
    vI = lstExisting.SelectedItem.Index
    vID = lstExisting.SelectedItem
 
    txtName = lstExisting.ListItems(vI).ListSubItems(1)
    txtAddress = lstExisting.ListItems(vI).ListSubItems(2)
    txtTelNum = lstExisting.ListItems(vI).ListSubItems(3)
    txtCity = lstExisting.ListItems(vI).ListSubItems(4)
End Sub
 
Private Sub txtSearchKey_Change()
 
Dim vSQL As String
 
    Select Case cboSearchBy.Text
        Case "Name"
            vSQL = "SELECT * FROM DirectoryList WHERE Name LIKE '" & txtSearchKey & "*'"
        Case "Address"
            vSQL = "SELECT * FROM DirectoryList WHERE Address LIKE '" & txtSearchKey & "*'"
        Case "Telephone #"
            vSQL = "SELECT * FROM DirectoryList WHERE TelNum LIKE '*" & txtSearchKey & "'"
        Case "City"
            vSQL = "SELECT * FROM DirectoryList WHERE City LIKE '" & txtSearchKey & "*'"
    End Select
 
    Call Show_Existing(vSQL)
 
End Sub