Option Compare Database
Option Explicit

' #Const VIEW_MODE = "INTERNET"
#Const VIEW_MODE = "INTRANET"

Private mcnn As ADODB.Connection
Private mrsTips As ADODB.Recordset
Private mrsType As ADODB.Recordset
Private mrstImages As ADODB.Recordset
Private mobjListItems As ListItems

' ********************
Private Sub Form_Load()
  Call OpenDatabaseConnection
  Call OpenTipsRecordset
  Call OpenTypeRecordset
  Call LoadListViewHeaders
  Call LoadListViewData
  Call LoadTreeView
End Sub

' ************************************
Private Sub Form_Unload(Cancel As Integer)
  Set mrsTips = Nothing
  Set mrsType = Nothing
End Sub

' *************************
Private Sub objListView_Click()
  objListView.SetFocus
End Sub

' **********************************************************
Private Sub objListView_ColumnClick(ByVal ColumnHeader As Object)
  Dim intCol As Integer
    
  intCol = ColumnHeader.Index - 1
  With objListView
    If (.SortKey = intCol) Then
      If .SortOrder = lvwAscending Then
        .SortOrder = lvwDescending
      Else
        .SortOrder = lvwAscending
      End If
    Else
      .SortKey = intCol
      .SortOrder = lvwAscending
    End If
    .Sorted = True
    .SelectedItem.EnsureVisible
  End With   
End Sub

' ****************************
Private Sub objListView_DblClick()
  With objListView
    Debug.Print Trim(.SelectedItem.Key) & " " & .SelectedItem
    #If VIEW_MODE = "ONLINE" Then
      Me!objIE.Navigate "http://www.friendlysw.com/knowhow/" & Trim(.SelectedItem.Key)
    #Else
      Me!objIE.Navigate "http://dellxps/knowhow/" & Trim(.SelectedItem.Key)
    #End If
  End With
End Sub

' ***********************************************
Private Sub objTreeView_Collapse(ByVal Node As Object)
    Call objTreeView_NodeClick(Node)
End Sub

' **********************************************
Private Sub objTreeView_Expand(ByVal Node As Object)
    Call objTreeView_NodeClick(Node)
End Sub

' *************************************************
Private Sub objTreeView_NodeClick(ByVal Node As Object)
  Dim strSQL As String
  
  mrsTips.Close
    
  If Node = "Tips" Then
    strSQL = "SELECT * FROM  tblTips"
  Else
    strSQL = "SELECT * FROM  tblTips WHERE TipType= '" & Node & "'"
  End If
  mrsTips.Open strSQL, mcnn, adOpenKeyset, adLockReadOnly
  Call LoadListViewData  
End Sub

' *******************************
Private Sub OpenDatabaseConnection()
  Set mcnn = New ADODB.Connection
  mcnn.ConnectionString = _
     "Provider=Microsoft.Jet.OLEDB.4.0; " & _
     "Data Source=" & CurrentProject.Connection
  mcnn.Open
End Sub

' ***************************
Private Sub OpenTipsRecordset()
  Dim strSQL As String
  
  strSQL = "SELECT tblTips.TipID, tblTips.TipType, tblTips.TipTitle," _
         & "tblTips.TipURL, tblTips.TipAdded, tblTips.TipUpdated" _
         & " FROM tblTips;"

  Set mrsTips = New ADODB.Recordset
  mrsTips.Open strSQL, mcnn, adOpenStatic, adLockOptimistic
End Sub

' ***************************
Private Sub OpenTypeRecordset()
  Dim strSQL As String
  
  strSQL = "SELECT tblType.TypeID, tblType.TypeName" _
         & " FROM tblType;"
         
  Set mrsType = New ADODB.Recordset
  mrsType.Open strSQL, mcnn, adOpenKeyset, adLockOptimistic
End Sub

' **********************
Private Sub LoadTreeView()
  Dim objTreeView As Control
  Dim objNode As Node

  Set objTreeView = Me.objTreeView
  Set objNode = objTreeView.Nodes.Add(, , "Tips", "Tips", 6)

  With mrsType
    .MoveFirst
    Do Until .EOF
        With objTreeView.Nodes
          Set objNode = .Add("Tips", tvwChild, , Trim(mrsType!TypeName), Int(mrsType!TypeID))
        End With
        .MoveNext
    Loop
  End With
End Sub

' ****************************
Private Sub LoadListViewHeaders()

  Dim objListItem As ListItem

  Set mobjListItems = Me.objListView.ListItems                   
  
  With Me.objListView
    .SmallIcons = objImageType.Object
    .View = lvwReport
    With .ColumnHeaders
      .Add , , "Title", 8500
      .Add , , "Added", 1000
      .Add , , "Updated", 1000
    End With
  End With    
End Sub

' *************************
Private Sub LoadListViewData()

  Dim objListItem As ListItem

  mobjListItems.Clear

  With mrsTips
    .MoveFirst  
    Do Until .EOF
        Set objListItem = mobjListItems.Add(, mrsTips!TipURL, mrsTips!TipTitle) 
        With objListItem
            .SubItems(1) = mrsTips!TipAdded
            .SubItems(2) = Nz(mrsTips!TipUpdated, " ")
            .SmallIcon = Chr(34) & mrsTips!TipType & Chr(34)
        End With
        .MoveNext
    Loop
  End With
End Sub