Please Answer..3rd time Posting..Treeview Complication

[venkatraman_r]

HI ALL,
I have 3 tables with few fields..I need to create a tree view in the following structure..i tried to do it, but the output was not either exact or came out with errors;

Table 1 -------(Root Item)

Field 1 -------(Child Item)
SubField 1-------(Sub Child Item)
SubField 2

Field 2
SubField 1
SubField 2
-------
Table 2 -------(Root Item)
Field 1 -------(Child Item)
SubField 1-------(Sub Child Item)
SubField 2

Field 2
SubField 1
SubField 2

Please help asap.


------------------
Regards,

Venkat

venkatraman_r@hotmail.com
ICQ: 45714766
http://venkat.iscool.net

[DiGiTaIErRoR]

Post your source code, easier to fix code then to write your own....

------------------
DiGiTaIErRoR

[venkatraman_r]

Here is my code;

-------
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim node As Nodes
Dim i As Integer
Dim j As Integer

Private Sub Form_Load()
Dim tables As TableDefs
Set db = OpenDatabase("d:\personal\library.mdb")
Set rs1 = db.OpenRecordset("books", dbOpenDynaset)
Set rs1 = db.OpenRecordset("customer", dbOpenDynaset)
Set rs1 = db.OpenRecordset("transaction", dbOpenDynaset)
rs1.MoveFirst

On Error Resume Next
tvwTest.Indentation = 200
tvwTest.Nodes.Clear

For i = 0 To db.TableDefs.Count - 1
For j = 0 To db.TableDefs(i).Fields.Count - 1
If db.TableDefs(i).Attributes = 0 Then
Set node = tvwTest.Nodes.Add(, , "TABLE" & Trim(Str(i + 1)), db.TableDefs(i).Name)
Set node = tvwTest.Nodes.Add("TABLE" & Trim(Str(i + 1)), tvwChild, "TABLE" & Trim(Str(j + 1)), db.TableDefs(i).Fields(j).Name)
End If
Next j
Next i

End Sub


------------------
Regards,

Venkat

venkatraman_r@hotmail.com
ICQ: 45714766
http://venkat.iscool.net

[DiGiTaIErRoR]

you dim rs1 rs2 and rs3 yet you only use rs1 is it possible you need to change:
Set rs1 = db.OpenRecordset("books", dbOpenDynaset)
Set rs1 = db.OpenRecordset("customer", dbOpenDynaset)
Set rs1 = db.OpenRecordset("transaction", dbOpenDynaset)

to:

Set rs1 = db.OpenRecordset("books", dbOpenDynaset)
Set rs2 = db.OpenRecordset("customer", dbOpenDynaset)
Set rs3 = db.OpenRecordset("transaction", dbOpenDynaset)

Good Luck!


------------------
DiGiTaIErRoR

[Mark Sreeves]

Venkat

this bit of your code:


For i = 0 To db.TableDefs.Count - 1
For j = 0 To db.TableDefs(i).Fields.Count - 1
If db.TableDefs(i).Attributes = 0 Then
Set node = tvwTest.Nodes.Add(, , "TABLE" & Trim(Str(i + 1)), db.TableDefs(i).Name)
Set node = tvwTest.Nodes.Add("TABLE" & Trim(Str(i + 1)), tvwChild, "TABLE" & Trim(Str(j + 1)), db.TableDefs(i).Fields(j).Name)
End If
Next j
Next i




Have you got the Next j in the right place?

Why have you got On Error Resume Next in there? What errors were you getting?


and on a minor note:
personally, I would stick "TABLE" & Trim(Str(i + 1)) into a variable because you are repeating the trim() a LOT of times!



------------------
Mark Sreeves
Analyst Programmer

Mark.Sreeves@Softlab.co.uk
A BMW Group Company

[Mark Sreeves]

err... in fact what is Trim(Str(i + 1)) in there for?



------------------
Mark Sreeves
Analyst Programmer

Mark.Sreeves@Softlab.co.uk
A BMW Group Company

[venkatraman_r]

Hi DiGiTaIErRoR and Mark,

The rs1 repeated thrice is a typing mistake...Its actually rs1,rs2 and rs3 only. So, no problem could be with that thing.

And Mark,

The i and j had been placed in the correct spot only. I am getting the output which is as follows;
Out of the 3 tables with 3 columns each;

Only 1st and 3rd tables are displayed..The first table carries the values of the third table and all that junk mixes up together..Totally, the outcome is not as expected.

Any help?? :)



------------------
Regards,

Venkat

venkatraman_r@hotmail.com
ICQ: 45714766
http://venkat.iscool.net

[andymac]

Hi,
From your initial example, what you seem to be trying to create is a record of each transaction for each customer by book;

I,e.,

Book
Customer
Transaction

Is this correct ?

Ta

Andy

[Serge]

Venkat, can you send me your database files (in ZIP foramt), so I can see the structure of the database. I'm sure it is very easy to do what you're asking, but it is a bit hard to do it blind folded.

------------------

Serge

Programmer Analyst
sdymkov@microage.com
Access8484@aol.com
ICQ#: 51055819 (http://www.icq.com/51055819)

[venkatraman_r]

Hi Serge, Pls check the mail..i am sending u the file in another few mins.

And andymac,

What u say is correct..but i need to include the fields also below the table names as child items..

Sincere Thanx to all u guys for ur helping tendency.

:) :)



------------------
Regards,

Venkat

venkatraman_r@hotmail.com
ICQ: 45714766
http://venkat.iscool.net

[Serge]

Here is the modified code:


Dim db As Database
Dim rsCustomer As Recordset
Dim rsBooks As Recordset
Dim rsTrans As Recordset
Dim nodCustomer As Node
Dim nodBooks As Node
Dim nodTrans As Node

Set db = Workspaces(0).OpenDatabase("D:\VB Prj\treeview\library.mdb")
Set rsCustomer = db.OpenRecordset("Customer", dbOpenTable)
With TreeView1
.Indentation = 250
.LineStyle = tvwRootLines
Do Until rsCustomer.EOF
Set nodCustomer = .Nodes.Add(, , "C" & CStr(rsCustomer("CustId")), rsCustomer("Custname"))
Set rsTrans = db.OpenRecordset("Select * from Transaction Where custid =" & rsCustomer("custid"), dbOpenSnapshot)
Do Until rsTrans.EOF
Set nodTrans = .Nodes.Add(nodCustomer, tvwChild, , rsTrans("transid"))
Set rsBooks = db.OpenRecordset("Select * from books where bookid=" & rsTrans("bookid"), dbOpenSnapshot)
Do Until rsBooks.EOF
Set nodBooks = .Nodes.Add(nodTrans, tvwChild, , rsBooks("bookname"))
rsBooks.MoveNext
Loop
rsTrans.MoveNext
Loop
rsCustomer.MoveNext
Loop
End With


Note: I modified your Transaction table to include CustID.

This example will build the TreeView as follows:


Customer
|__Transaction
|__Books



I also sent you this code by email.

------------------

Serge

Programmer Analyst
sdymkov@microage.com
Access8484@aol.com
ICQ#: 51055819 (http://www.icq.com/51055819)