Need help on Treeview code

[Guest]

Hi,everyone ,I 've got a Treeview code which are not working properly.
Actually, the treeview works fine, but when I click on the node ,it always
comes a error "Mistype" with this line -strSQL = strSQL & "WHERE Daterec = #"
& CDate(Node.Tag) & "# "

Here, is my code ,can anyone help me to fix it up ? Thanks very much

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 = Format$(rst.Fields("DateRec").Value, "yyyy-mm-dd")
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 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 Detail_Click()

End Sub

Private Sub Form_Load()
AddMyTree
End Sub

 

[Douglas J. Steele]

I don't believe you need the CDate. You're trying to build a string.

Try:

strSQL = strSQL & "WHERE Daterec = #" & Node.Tag & "# "

or

strSQL = strSQL & "WHERE Daterec = " & Format(CDate(Node.Tag),
"\#mm\/dd\/yyyy\#")

 

[Guest]

Hi,Douglas ,thanks for your reply. Unfor. neither of the way works.
with strSQL = strSQL & "WHERE Daterec = #" & Node.Tag & "# "
I got "Synax error, Date=##"

with strSQL = strSQL & "WHERE Daterec = " & Format(CDate(Node.Tag),
"\#mm\/dd\/yyyy\#")
I got "type mismatch"

I've been struggled this for days,It really drives me crazy.

 

[Douglas J. Steele]

The "Syntax error, Date=##" implies that there's no value in the Tag
property.

Try stripping the "a" off the Key value and using that instead.

 

[Guest]

Hi,Douglas J. Is this the format ?
If so, I still got the error meg "Synax error,DateRec=##"

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 _
("DateRec=##" & Format(rst("DateRec").Value, "yyyy"))
On Error GoTo Err_AddMyTree
If nodeYear Is Nothing Then
Set nodeYear = objTree.Nodes _
..Add(, , "DateRec=##"& 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 _
("DateRec=##" & Format(rst("DateRec").Value, "yyyymm"))
On Error GoTo Err_AddMyTree
If nodeYearMonth Is Nothing Then
Set nodeYearMonth = objTree.Nodes _
..Add(nodeYear, tvwChild, _
"DateRec=##" & Format(rst("DateRec").Value, "yyyymm"), _
Format(rst("DateRec").Value, "mmmm"), 1, 2)
End If

Set nodCurrent = objTree.Nodes _
..Add(nodeYearMonth, tvwChild, _
"DateRec=##"& rst("DateRec").Value, _
rst("DateRec").Value, 1, 2)
nodCurrent.Tag = Format$(rst.Fields("DateRec").Value, "yyyy-mm-dd")
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 TreeView0_NodeClick(ByVal Node As Object)
Dim strSQL As String
strSQL = "SELECT * FROM FinalQuery "
strSQL = strSQL & "WHERE Daterec = #" & Node.Tag & "# "

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

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()
AddMyTree
End Sub

 

[Jamie Collins]

with strSQL = strSQL & "WHERE Daterec = #" & Node.Tag & "# "
I got "Synax error, Date=##"

Some of the nodes don't have tags; specifically, those at the top two
levels. Your _NodeClick event doesn't differentiate between nodes at
different levels, whereas it should only operate on the tags of the
lowest level nodes.

The quickest fix might be:

Private Sub TreeView0_NodeClick(ByVal Node As Object)
If Len(Node.Tag) = 0 Then
Exit Sub
End If
Dim strSQL As String
etc etc

Personally <g>, I would put explicit tags on all nodes even if it was
e.g. 'Level1' to know to disregard such a tag in code.

Jamie.

--

 

[Guest]

Thanks,Jamie ,Thanks Douplas

Finally, it works !!!!