Treeview problem ,So strange !

[x6v87qe]

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 !

[Jamie Collins]

x6v87qe wrote:
> 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

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.

--

[Jamie Collins]

Jamie Collins wrote:
> x6v87qe wrote:
> > 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
>
> Something like:

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.

--

[Jamie Collins]

Jamie Collins wrote:
> x6v87qe wrote:
> > 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
>
> Something like:

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

[x6v87qe]

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

[x6v87qe]

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

[Jamie Collins]

x6v87qe wrote:
> 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

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.

--

[x6v87qe]

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"

[Jamie Collins]

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

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.

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.

--

[x6v87qe]

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

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
_____________________________________

[Jamie Collins]

x6v87qe wrote:
> 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

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.

--

[x6v87qe]

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