Using TreeView and TextBox to Display Records from MDB File
I wrote an article earlier about using
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.
My Project (
prjPhoneIndex) has one Form (
frmPhone). I add the following controls to my Form:
tvData) to display (
Name) and (
TextBoxes as array (
txtField(2)) to display Fields of any Record
ImageList1) to load some icons for our
- Seven Buttons:
TreeViewwith database file
cmdAddto add new Record to database file and add this Record to
cmdEditto rewrite any Field
cmdUpdateto save Record after Add new one or Edit any one
cmdCancelto cancel Add or cancel Edit
cmdDeleteto remove Record from database file and remove this Record from
cmdCloseto 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_Load Sub: to connect with database file
cmdLoadData_Click Sub: to set the Nodes of
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
When you create a new project, you must add
ImageList controls to
ToolBox from Components dialog:
Microsoft Windows Common Controls 6.0
You must add the reference: “
Microsoft ActiveX Data Objects 2.x“.
I hope this article is useful and helps you to display Records from database using
Please tell me if you have any ideas or if you find any problems. Thanks to The Code Project and thanks to all.
- 26th October, 2009: Initial post