Treeview problem ,So strange !

G

Guest

hi,everyone. I've got a problem with my treeview control.It's not happed all
the time
but for some of the computers. I used "date" as the node point for the
treeview,when click on a particular date , another subform will display
thecorresponding infor on this date. it works fine on my computer,HOWEVER,in
some other computers, it behavours like this:
1-7/8/2006- no disp, 8/8/2006-ok,9-12/8/2006-no dis, rest -ok
1-8/9/2006- no disp, 9/9/2006-ok,10-12/9/2006-no dis, rest -ok
1-9/10/2006- no disp, 10/10/2006-ok,11-12/10/2006-no dis, rest -ok
1-10/8/2006- no disp, 11/11/2006-ok,12/11/2006-no dis, rest -ok

Isn't it wierd ?!!! Here is my code:
___________________________________________
Sub AddMyTree()
On Error GoTo Err_AddMyTree
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim nodCurrent As Node
Dim objTree As Object

Set objTree = Me.TreeView0
Set conn = CurrentProject.Connection

strSQL = "SELECT DISTINCT DateRec FROM Table1 GROUP BY DateRec"

rst.Open strSQL, conn, adOpenStatic, adLockReadOnly

Do While Not rst.EOF
Set nodCurrent = objTree.Nodes.Add(, , "a" & rst("DateRec"),
rst("DateRec"), 1, 2)
nodCurrent.Tag = rst("DateRec")
rst.MoveNext
Loop



rst.Close
Set rst = Nothing
Set conn = Nothing

Exit_AddMyTree:
Exit Sub

Err_AddMyTree:
Set rst = Nothing
Set conn = Nothing
MsgBox Err.Description, vbCritical, "AddMyTree"
Resume Exit_AddMyTree

End Sub

Private Sub Detail_Click()

End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL1 = "SELECT * FROM Table1 "
strSQL = strSQL1 & "WHERE DateRec = # " & CDate(Node.Tag) & " # "

Me.Table1_subform.Form.RecordSource = strSQL
Me.Table1_subform.Form.Requery

End Sub


Private Sub Form_Load()

AddMyTree

End Sub
__________________________________________

Can any body tell me what may be the problem ? I been struglling this for
days .
and
if possible can any one give me an example on how to make the treeview to be
displayed on a "year","Month","date" ,three levels grade, currently it just
displayed all the dates in the same manner

Many thanks !
 
J

Jamie Collins

x6v87qe said:
if possible can any one give me an example on how to make the treeview to be
displayed on a "year","Month","date" ,three levels grade, currently it just
displayed all the dates in the same manner
Click to expand...

Something like:

Dim nodeYear As Node
Dim nodeYear As Node
Dim nodeYearMonth As Node

Do While Not rst.EOF
Set nodeYear = Nothing
On Error Resume Next
Set nodeYear = objTree.Nodes _
("a" & Format(rst("DateRec").Value, "yyyy"))
On Error GoTo Err_AddMyTree
If nodeYear Is Nothing Then
Set nodeYear = objTree.Nodes _
.Add(, , "a" & Format(rst("DateRec").Value, "yyyy"), _
Format(rst("DateRec").Value, "yyyy"), 1, 2)
End If

Set nodeYearMonth = Nothing
On Error Resume Next
Set nodeYearMonth = objTree.Nodes _
("a" & Format(rst("DateRec").Value, "yyyymm"))
On Error GoTo Err_AddMyTree
If nodeYearMonth Is Nothing Then
Set nodeYearMonth = objTree.Nodes _
.Add(nodeYear, tvwChild, _
"a" & Format(rst("DateRec").Value, "yyyymm"), _
Format(rst("DateRec").Value, "mmmm"), 1, 2)
End If

Set nodCurrent = objTree.Nodes _
.Add(nodeYearMonth, tvwChild, _
"a" & rst("DateRec").Value, _
rst("DateRec").Value, 1, 2)
nodCurrent.Tag = rst("DateRec")
rst.MoveNext
Loop

Jamie.

--
 
J

Jamie Collins

Jamie said:
Something like:
Click to expand...

Well, I didn't like trying to add parent nodes on each iteration. Could
be better to get a hierarchical recordset to begin with and only add
the parent nodes when needed e.g.

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=MSDataShape;Data " & _
CurrentProject.Connection.ConnectionString
conn.Open

strSQL = _
"SHAPE {SELECT DISTINCT YEAR(DateRec) AS" & _
" DateRecYear FROM Table1} AS chapRecDateYears" & _
" APPEND ((SHAPE {SELECT DISTINCT YEAR(DateRec)" & _
" AS DateRecYear, MONTH(DateRec) AS DateRecMonth" & _
" FROM Table1} APPEND ({SELECT DateRec, YEAR(DateRec)" & _
" AS DateRecYear, MONTH(DateRec) AS DateRecMonth" & _
" FROM Table1} AS chapRecDate RELATE DateRecYear" & _
" TO DateRecYear, DateRecMonth TO DateRecMonth))" & _
" AS chapRecDateYearsMonths RELATE DateRecYear" & _
" TO DateRecYear)"

rst.Open strSQL, conn, adOpenStatic, adLockReadOnly

Dim nodeYear As Node
Dim nodeYearMonth As Node

With rst
Do While Not .EOF
Set nodeYear = objTree.Nodes.Add( _
, , "a" & .Fields("DateRecYear").Value, _
.Fields("DateRecYear").Value, 1, 2)
With .Fields("chapRecDateYearsMonths").Value
Do While Not .EOF
Set nodeYearMonth = objTree.Nodes.Add( _
nodeYear, tvwChild, _
"a" & .Fields("DateRecYear").Value & _
.Fields("DateRecMonth").Value, _
.Fields("DateRecMonth").Value, 1, 2)
With .Fields("chapRecDate").Value
Do While Not .EOF
Set nodCurrent = objTree.Nodes.Add( _
nodeYearMonth, tvwChild, _
"a" & .Fields("DateRec").Value, _
.Fields("DateRec").Value, 1, 2)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With

Jamie.

--
 
J

Jamie Collins

Jamie said:
Something like:
Click to expand...

Well, I didn't like trying to add parent nodes on each iteration. Could
be better to get a hierarchical recordset to begin with and only add
the parent nodes when needed e.g.

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=MSDataShape;Data " & _
CurrentProject.Connection.ConnectionString
conn.Open

strSQL = _
"SHAPE {SELECT DISTINCT YEAR(DateRec) AS" & _
" DateRecYear FROM Table1} AS chapRecDateYears" & _
" APPEND ((SHAPE {SELECT DISTINCT YEAR(DateRec)" & _
" AS DateRecYear, MONTH(DateRec) AS DateRecMonth" & _
" FROM Table1} APPEND ({SELECT DateRec, YEAR(DateRec)" & _
" AS DateRecYear, MONTH(DateRec) AS DateRecMonth" & _
" FROM Table1} AS chapRecDate RELATE DateRecYear" & _
" TO DateRecYear, DateRecMonth TO DateRecMonth))" & _
" AS chapRecDateYearsMonths RELATE DateRecYear" & _
" TO DateRecYear)"

rst.Open strSQL, conn, adOpenStatic, adLockReadOnly

Dim nodeYear As Node
Dim nodeYearMonth As Node

With rst
Do While Not .EOF
Set nodeYear = objTree.Nodes.Add( _
, , "a" & .Fields("DateRecYear").Value, _
.Fields("DateRecYear").Value, 1, 2)
With .Fields("chapRecDateYearsMonths").Value
Do While Not .EOF
Set nodeYearMonth = objTree.Nodes.Add( _
nodeYear, tvwChild, _
"a" & .Fields("DateRecYear").Value & _
.Fields("DateRecMonth").Value, _
.Fields("DateRecMonth").Value, 1, 2)
With .Fields("chapRecDate").Value
Do While Not .EOF
Set nodCurrent = objTree.Nodes.Add( _
nodeYearMonth, tvwChild, _
"a" & .Fields("DateRec").Value, _
.Fields("DateRec").Value, 1, 2)
.MoveNext
Loop
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With

Jamie.

--
 
G

Guest

Great !! thanks Jamie! ,bothe of the way works, but how should I modeify the

Click even to make it possible to be displayed on a subform

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL1 = "SELECT * FROM Table1 "
strSQL = strSQL1 & "WHERE DateRec = # " & CDate(Node.Tag) & " # "

Me.Table1_subform.Form.RecordSource = strSQL
Me.Table1_subform.Form.Requery

End Sub

this won't work please help
 
G

Guest

Great Jamie ! both the way works. How can I modeify the click procedure to
make it displayed on the ssub form

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL1 = "SELECT * FROM Table1 "
strSQL = strSQL1 & "WHERE DateRec = # " & CDate(Node.Tag) & " # "

Me.Table1_subform.Form.RecordSource = strSQL
Me.Table1_subform.Form.Requery

End Sub

This just not woking,please help
 
J

Jamie Collins

x6v87qe said:
How can I modeify the click procedure to
make it displayed on the ssub form

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL1 = "SELECT * FROM Table1 "
strSQL = strSQL1 & "WHERE DateRec = # " & CDate(Node.Tag) & " # "

Me.Table1_subform.Form.RecordSource = strSQL
Me.Table1_subform.Form.Requery

End Sub

This just not woking
Click to expand...

Oops! I forgot to set the tags. After the line:

Set nodCurrent = objTree.Nodes.Add(...)

add something like:

nodCurrent.Tag = .Fields("DateRec").Value

In the _Click event, remember to check that you are at the appropriate
level in the tree to use the tag as a date. It may be an idea to also
set the tags at the nodeYearMonth and nodeYearMonth levels
respectively.

Jamie.

--
 
G

Guest

Hi,Jamie
I did as u said it, comes out an error "Invild or unquantified reference" ?
for .Fields
Please also tell me how to write the click event, I coy that code form
aomewhere else. I have no idea of that at all. Thanks

"Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL = "SELECT * FROM FinalQuery "
strSQL = strSQL & "WHERE Daterec = #" & CDate(Node.Tag) & "# "

Me.FinalQuery_subform.Form.RecordSource = strSQL
Me.FinalQuery_subform.Form.Requery
End Sub"
 
J

Jamie Collins

x6v87qe said:
I did as u said it, comes out an error "Invild or unquantified reference" ?
for .Fields
Click to expand...

OK so you're not using the hierarchical example. The equivalent line
is:

nodCurrent.Tag = rst.Fields("DateRec").Value
Please also tell me how to write the click event, I coy that code form
aomewhere else. I have no idea of that at all.
Click to expand...

I'm not a Forms expert but from memory you select the treeview, right
click, choose 'Build event' from the context menu which should take you
to Visual Basic Editor, with the control name selected in the dropdown
on the left (top left of the code pane), choose Click in the dropdown
on the right.

Jamie.

--
 
G

Guest

Thanks for your reply ,Jamie. ! Sorry, I thought I may misguid you. The
problem I have was : don't know how to write the code in
"TreeView0_NodeClick" .Treeview was fine ,but when I click the on the node.
it always comes "Type mismatch". This is the code I have :
_____________________________________
Option Compare Database

Sub AddMyTree()
On Error GoTo Err_AddMyTree
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim nodCurrent As Node
Dim objTree As Object

Set objTree = Me.TreeView0
Set conn = CurrentProject.Connection

strSQL = "SELECT DISTINCT DateRec FROM FinalQuery GROUP BY DateRec;"

rst.Open strSQL, conn, adOpenStatic, adLockReadOnly


Dim nodeYear As Node
Dim nodeYearMonth As Node

Do While Not rst.EOF
Set nodeYear = Nothing
On Error Resume Next
Set nodeYear = objTree.Nodes _
("a" & Format(rst("DateRec").Value, "yyyy"))
On Error GoTo Err_AddMyTree
If nodeYear Is Nothing Then
Set nodeYear = objTree.Nodes _
.Add(, , "a" & Format(rst("DateRec").Value, "yyyy"), _
Format(rst("DateRec").Value, "yyyy"), 1, 2)

End If

Set nodeYearMonth = Nothing
On Error Resume Next
Set nodeYearMonth = objTree.Nodes _
("a" & Format(rst("DateRec").Value, "yyyymm"))
On Error GoTo Err_AddMyTree
If nodeYearMonth Is Nothing Then
Set nodeYearMonth = objTree.Nodes _
.Add(nodeYear, tvwChild, _
"a" & Format(rst("DateRec").Value, "yyyymm"), _
Format(rst("DateRec").Value, "mmmm"), 1, 2)
End If

Set nodCurrent = objTree.Nodes _
.Add(nodeYearMonth, tvwChild, _
"a" & rst("DateRec").Value, _
rst("DateRec").Value, 1, 2)
nodCurrent.Tag = rst.Fields("DateRec").Value

rst.MoveNext

Loop




rst.Close
Set rst = Nothing
Set conn = Nothing

Exit_AddMyTree:
Exit Sub

Err_AddMyTree:
Set rst = Nothing
Set conn = Nothing
MsgBox Err.Description, vbCritical, "AddMyTree"
Resume Exit_AddMyTree

End Sub



Private Sub Detail_Click()

End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL = "SELECT * FROM FinalQuery "
strSQL = strSQL & "WHERE Daterec = #" & CDate(Node.Tag) & "# "

Me.FinalQuery_subform.Form.RecordSource = strSQL
Me.FinalQuery_subform.Form.Requery
End Sub



Private Sub Form_Load()
AddMyTree
End Sub
_____________________________________
 
J

Jamie Collins

x6v87qe said:
when I click the on the node.
it always comes "Type mismatch". This is the code I have :

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL = "SELECT * FROM FinalQuery "
strSQL = strSQL & "WHERE Daterec = #" & CDate(Node.Tag) & "# "

Me.FinalQuery_subform.Form.RecordSource = strSQL
Me.FinalQuery_subform.Form.Requery
End Sub
Click to expand...

You must isolate which part of which line is causing the error. My
guess would be

CDate(Node.Tag)

Put a breakpoint in the event and in the Immediate Window try:

? CDate(Node.Tag)

It works OK for me but then I tested with Table1.DateRec being a
DATETIME column ;-) Even so I think I'd be happier writing the tag in
ISO format e.g.

nodCurrent.Tag = Format$(rst.Fields("DateRec").Value, "yyyy-mm-dd
hh:nn:ss")

Jamie.

--
 
G

Guest

My dear GOD !!! still not working, This time no error messege come out ,but
the sub form was not querying at all.

That's what I have
________
Set nodCurrent = objTree.Nodes _
..Add(nodeYearMonth, tvwChild, _
"a" & rst("DateRec").Value, _
rst("DateRec").Value, 1, 2)
nodCurrent.Tag = Format$(rst.Fields("DateRec").Value, "yyyy-mm-dd ')
___________

____

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL = "SELECT * FROM FinalQuery "
strSQL = strSQL & "WHERE Daterec = #" & CDate(Node.Tag) & "# "

Me.FinalQuery_subform.Form.RecordSource = strSQL
Me.FinalQuery_subform.Form.Requery
End Sub
_____

Jamie, would you be kind enough to paste a full code ? I almost dead of it
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help on Treeview code 6
Problem with Treeview code 3
Exclude current record of a select 5
Challenging Recordset 1
populate treeview using replication ID (GUID) 10
Problem with Tree View 2
StringFromGUID with empty replication ID 3
Filtering Sub Form 1

Top