Hi everyone,
i have been working on a code for a few weeks and am completely lost. I have a Tree which contains the code below. instead of the tree displaying the information in the table i would like there to be a 'branch' before this step which says "custodian History". i would ideally click on custodian history to display the assetnumber table values.
Please help! im loosing my mind
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strQuery1 As String
Dim strQuery2 As String
Dim nod As Object
Dim strNode1Text As String
Dim strNode2Text As String
Dim strVisibleText As String
Dim strMessage As String
Dim intVBMsg As Integer
Dim intCounter As Integer
Dim stringcount As String
intCounter = 1
Set db = CurrentDb()
strQuery1 = "select DISTINCT AssetNumber from [Asset Master]"
With Me![Custodian History Form]
Set rs = db.OpenRecordset(strQuery1) ', dbOpenForwardOnly)
Do Until rs.EOF
Debug.Print rs![AssetNumber]
strNode1Text = StrConv("Level1" & rs![AssetNumber], _
vbLowerCase)
Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rs![AssetNumber])
nod.Expanded = False
strQuery2 = "select DISTINCT AssetNumber, [Asset Name] from [Asset Master] where [AssetNumber] = " & nod.Text
Set rs2 = db.OpenRecordset(strQuery2)
Do Until rs2.EOF
strNode2Text = StrConv("Level2" & rs2![Asset Name], vbLowerCase)
strVisibleText = rs2!AssetNumber
'if modelname is null, will have problem
If IsNull(rs2!AssetNumber) Then
MsgBox "number is null"
strVisibleText = "No Name"
End If
stringcount = CStr(intCounter)
strNode2Text = strNode2Text + stringcount
Set nod = .Nodes.Add(relative:=strNode1Text, relationship:=tvwChild, _
Key:=strNode2Text, Text:=strVisibleText)
rs2.MoveNext
Loop
rs2.Close
rs.MoveNext
Loop
rs.Close
End With
End Sub
I think i have to add in a line like this "Set nodRoot = .Nodes.Add(Key:="custhist",Text:="Custodian History")" or a line like this "Me.[Custodian History Form].Nodes.Add Text:="Custodian History", Key:="CustodianHistory"" But i do not no where or how.
Thank you in advance
i have been working on a code for a few weeks and am completely lost. I have a Tree which contains the code below. instead of the tree displaying the information in the table i would like there to be a 'branch' before this step which says "custodian History". i would ideally click on custodian history to display the assetnumber table values.
Please help! im loosing my mind
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strQuery1 As String
Dim strQuery2 As String
Dim nod As Object
Dim strNode1Text As String
Dim strNode2Text As String
Dim strVisibleText As String
Dim strMessage As String
Dim intVBMsg As Integer
Dim intCounter As Integer
Dim stringcount As String
intCounter = 1
Set db = CurrentDb()
strQuery1 = "select DISTINCT AssetNumber from [Asset Master]"
With Me![Custodian History Form]
Set rs = db.OpenRecordset(strQuery1) ', dbOpenForwardOnly)
Do Until rs.EOF
Debug.Print rs![AssetNumber]
strNode1Text = StrConv("Level1" & rs![AssetNumber], _
vbLowerCase)
Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rs![AssetNumber])
nod.Expanded = False
strQuery2 = "select DISTINCT AssetNumber, [Asset Name] from [Asset Master] where [AssetNumber] = " & nod.Text
Set rs2 = db.OpenRecordset(strQuery2)
Do Until rs2.EOF
strNode2Text = StrConv("Level2" & rs2![Asset Name], vbLowerCase)
strVisibleText = rs2!AssetNumber
'if modelname is null, will have problem
If IsNull(rs2!AssetNumber) Then
MsgBox "number is null"
strVisibleText = "No Name"
End If
stringcount = CStr(intCounter)
strNode2Text = strNode2Text + stringcount
Set nod = .Nodes.Add(relative:=strNode1Text, relationship:=tvwChild, _
Key:=strNode2Text, Text:=strVisibleText)
rs2.MoveNext
Loop
rs2.Close
rs.MoveNext
Loop
rs.Close
End With
End Sub
I think i have to add in a line like this "Set nodRoot = .Nodes.Add(Key:="custhist",Text:="Custodian History")" or a line like this "Me.[Custodian History Form].Nodes.Add Text:="Custodian History", Key:="CustodianHistory"" But i do not no where or how.
Thank you in advance
