Option Compare Database
Option Explicit

Private mcnn As ADODB.Connection
Private mrsCustomer As ADODB.Recordset
Private mrsKen As ADODB.Recordset
Private mobjListItems As ListItems

' *******************
Private Sub Form_Load()
  Call OpenDatabaseConnection
  Call OpenCustomerRecordset
  Call OpenKenRecordset
  Call LoadListViewHeaders
  Call LoadListViewData
  Call LoadTreeView         
End Sub

' *************************************
Private Sub Form_Unload(Cancel As Integer)  
  Set mrsCustomer = Nothing
  Set mrsKen = Nothing
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 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 OpenDatabaseConnection()
  Set mcnn = New ADODB.Connection
  mcnn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & CurrentProject.Connection
  mcnn.Open    
End Sub

' ********************************
Private Sub OpenCustomerRecordset()
  Dim strSQL As String
    
  strSQL = "SELECT 得意先.得意先コード, 得意先.得意先名, 得意先.担当者名," _
         & " 得意先.都道府県, 得意先.電話番号, 得意先.ファクシミリ" _
         & " FROM 得意先;"

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

' ***************************
Private Sub OpenKenRecordset()
  Dim strSQL As String   
    
  strSQL = "SELECT 得意先.都道府県" _
         & " FROM 得意先" _
         & " GROUP BY 得意先.都道府県;"    
    
  Set mrsKen = New ADODB.Recordset
  mrsKen.Open strSQL, mcnn, adOpenKeyset, adLockOptimistic
End Sub

' *****************************
Private Sub LoadListViewHeaders()
  Dim objListItem As ListItem

  Set mobjListItems = Me.objListView.ListItems
  With Me.objListView
    .View = lvwReport
    With .ColumnHeaders
      .Add , , "得意先名", 2880
      .Add , , "担当者名", 1440
      .Add , , "Tel", 1350
      .Add , , "Fax", 1350
    End With
  End With    
End Sub

' **************************
Private Sub LoadListViewData()   
  Dim objListItem As ListItem

  mobjListItems.Clear

  With mrsCustomer
    If .EOF Or .BOF Then
      Exit Sub
    End If
    .MoveFirst
    Do Until .EOF
      Set objListItem = mobjListItems.Add(, Left(!得意先名, 4), !得意先名)
      With objListItem
        .SubItems(1) = Nz(mrsCustomer!担当者名, "無")
        .SubItems(2) = Nz(mrsCustomer!電話番号, "無")
        .SubItems(3) = Nz(mrsCustomer!ファクシミリ, "無")
      End With
      .MoveNext
    Loop
  End With
End Sub

' *************************************************
Private Sub objTreeView_NodeClick(ByVal Node As Object)
  Dim strSQL As String
    
  mrsCustomer.Close
  If Node = "全国" Then
    strSQL = "SELECT * FROM 得意先;"
  Else
    strSQL = "SELECT * FROM 得意先 WHERE 都道府県 = '" & Node & "';"
  End If
  mrsCustomer.Open strSQL, mcnn, adOpenKeyset, adLockReadOnly
  Call LoadListViewData                     
End Sub

' ***********************
Private Sub LoadTreeView()
  Dim objTreeView As Control
  Dim objNode As Node
    
  Set objTreeView = Me.objTreeView
  With mrsKen
    .MoveFirst
    Set objNode = objTreeView.Nodes.Add(, , "全国", "全国")
    Do Until .EOF
        With objTreeView.Nodes
            Set objNode = .Add("全国", tvwChild, , mrsKen!都道府県)
        End With
        .MoveNext
    Loop
  End With
End Sub