Using TreeView and TextBox to Display Records from MDB File

• Download source - 16.75 KB

Introduction

I wrote an article earlier about using TreeView control, ListView control and ADO to display data from a database file (*.mdb), using TreeView to display Table name and Field name, ListView to display all Records of any Table when user selects this Table from TreeView. You can read this article if you click here.

In this new article, I replace ListView by a group of text boxes to display Records one by one from a database file (MyPhone.mdb) with one Table (Phone) containing three Fields (Name, Address, PhoneNumber) as Phone Index.

Now, I try to display my Phone Index using TreeView control and TextBox control and I try to Add new Records to my Phone Index or Edit any Field of Record or Delete undesirable Record from my Phone Index.

Background

My Project (prjPhoneIndex) has one Form (frmPhone). I add the following controls to my Form:

• TreeView (tvData) to display (Name) and (PhoneNumber)
• Three TextBoxes as array (txtField(0), txtField(1), txtField(2)) to display Fields of any Record
• ImageList (ImageList1) to load some icons for our TreeView
• Seven Buttons:
• cmdLoadData to populate TreeView with database file
• cmdAdd to add new Record to database file and add this Record to TreeView
• cmdEdit to rewrite any Field
• cmdUpdate to save Record after Add new one or Edit any one
• cmdCancel to cancel Add or cancel Edit
• cmdDelete to remove Record from database file and remove this Record from TreeView
• cmdClose to End show

Using the Code

First step: Connect with database file:

' You must define other variables in the Declarations part (see the code in my Form):
Dim strCon As String
Dim strSql As String

   ' In (Form_Load) procedure, we shall connect with database file:
    MyDataFile = App.Path & "\DataFile\" & "MyPhone.mdb"
    strCon = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDataFile & ";"
    strSql = "SELECT * FROM Phone ORDER BY Name"
    Set cn = New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.Open strCon
    Set oCmd = New ADODB.Command ' for delete undesirable records
    Set oCmd.ActiveConnection = cn
    oCmd.CommandType = adCmdText
    Set rs = New ADODB.Recordset
    rs.Open strSql, cn, adOpenStatic, adLockOptimistic

Second step: Bind TreeView control with data (one Node for customer name, other Node for phone number).

' Other variables are defined in Declarations part (see the code in my Form):
Dim CustName As String
Dim n As Integer
Dim c As Integer
Dim i As Integer
Dim r As Long

   ' See this code in (cmdLoadData_Click) procedure:
   ' set root node of TreeView.
   Set dbNode = tvData.Nodes.Add(, , "RootDB", "Phone Index", "imgIndexClose")
   dbNode.Tag = "RootDB"
   ' set table node:
   Set tabNode = tvData.Nodes.Add("RootDB", tvwChild, "PhoneTable", "Hello...", _
      & "imgPhoneClose")
   tabNode.Tag = "Table"
   ' start to set customer name as children of table node:
   c = tvData.Nodes("RootDB").Children
   ' first (Name)
   n = tvData.Nodes("RootDB").Child.FirstSibling.Index
   ' is database file empty?
   If rs.RecordCount = 0 Then
      Screen.MousePointer = vbDefault
      MsgBox "No records in database file, use (Add) to add rcords."
      Exit Sub  ' if database file empty exit sub.
   End If
   rs.MoveFirst
   For i = n To c + n
      ' get the field (Name of customer)
      Do Until rs.EOF
         CustName = rs.Fields("Name").Value
         ' display the field (Name of customer) as new Node
         Set fldNameNode = tvData.Nodes.Add("PhoneTable", tvwChild, "R-" & CustName, _
            & CustName, "imgRecord")
         fldNameNode.Tag = "Records"
         ' display phone number as new Node:
         r = rs.AbsolutePosition ' use this number as Key if we need.
         If rs.Fields("PhoneNumber").Value <> "" Then
            Set fldPhoneNode = tvData.Nodes.Add("R-" & CustName, tvwChild, _
               "N_" & Str(r), rs.Fields("PhoneNumber").Value, "imgPhoneOpen")
         End If
         rs.MoveNext
      Loop
   Next i
   rs.MoveFirst

Third step: What happen when the user clicks the Node of customer name?

Dim nodTag As String
Dim CustName As String
Dim i As Integer

   ' In (tvData_NodeClick) procedure we shall fill TextBox controls 
   ' with three Fields (Name, Address and PhoneNumber):
   nodTag = Node.Tag
   If nodTag = "Records" Then ' user click the node of customer name
      'read customer name
      CustName = Node.Text
      ' find customer name
      rs.MoveFirst
      rs.Find ("Name = " & "'" & CustName & "'")
      ' put the fields in TextBoxes
      For i = 0 To rs.Fields.Count - 1
         If rs.Fields(i).Value <> "" Then
            txtField(i).Text = rs.Fields(i).Value
         Else
            txtField(i).Text = ""
         End If
      Next i
   End If

You can read the full code in the Form (frmPhone):

• Form_Load Sub: to connect with database file
• cmdLoadData_Click Sub: to set the Nodes of TreeView
• tvData_Collapse Sub: to change icon
• tvData_Expand Sub: to change icon
• tvData_NodeClick Sub: to see how to fill TextBoxes with Fields (you can see the phone number when expanding without click)
• cmdAdd_Click Sub: to add new Record
• cmdEdit_Click Sub: to rewrite any Field
• cmdDelete_Click Sub: to remove Record
• cmdUpdate_Click Sub: to save Record after Add or Edit
• cmdCancel_Click Sub: to cancel Add or Edit

Remarks

When you create a new project, you must add TreeView and ImageList controls to ToolBox from Components dialog:
Microsoft Windows Common Controls 6.0
You must add the reference: "Microsoft ActiveX Data Objects 2.x".

Last Words

I hope this article is useful and helps you to display Records from database using TreeView and TextBox.
Please tell me if you have any ideas or if you find any problems. Thanks to The Code Project and thanks to all.

History

• 26^th October, 2009: Initial post

Mostafa Kaisoun
M_Kaisoun@hotmail.com