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

[email protected]
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

[email protected]
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

[email protected]
A BMW Group Company

[Mark Sreeves]

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



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

[email protected]
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

[email protected]
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
[email protected]
[email protected]
ICQ#: 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

[email protected]
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
[email protected]
[email protected]
ICQ#: 51055819