P
paul
I have a "Tree View" Active X control using the Microsoft Windows Common Control 6.0 (SP4) on a form. In the "Tree View" I have two other sub levels on it like a "file manager" with the top level like "Microsoft", then second level like "Office Suite", then third level like "Access XP" etc. Everything is working fine. The form is based on a table with four bounded text boxes and the record is filter out using the field on the third level on the "Tree View". Also one of the text box showing the same field "Access XP" as the third level on the "Tree View" . What I try to achieve is when user rename or edit that field to teh text box, it will refresh the field in the "Tree View". How to do it with the After_Update event to that text box?Thanks
Option Compare Database
Option Explicit
Private WithEvents oTV As TreeView
Private Sub Form_Load()
Randomize Timer 'So we get a different set of numbers each time
Set oTV = Me.TV.Object 'The key to everything is to get to the
'TreeView object, not to its OLE container
CreateTV
End Sub
Private Sub cmdCollapse_Click()
CreateTV 'Recreate the Treeview from scratch, which means
'that new values will automatically appear
Me.ImgCtrl.Picture = ""
Me.ImgCtrl.Visible = True 'and make sure the image is visible
Me.RecordSource = ""
Me.FileName.ControlSource = ""
Me.ImageName.ControlSource = ""
Me.Description.ControlSource = ""
Me.Status.ControlSource = ""
Me.txtImageID = Null
End Sub
Private Sub CreateTV()
Dim oNode As Node
Dim oNewNode As Node
Dim i As Integer
Dim rs As DAO.Recordset
Dim SQL As String
With oTV
.Nodes.Clear 'Get rid of any existing nodes
.Nodes.Add , , "TOP", "User - Date Imported - Images" 'Create a known key for the top level
'SQL = "Select DISTINCT UserID from Tbl_Image Order By Date"
SQL = "SELECT DISTINCT UserID FROM tblSecurity INNER JOIN Tbl_Image ON tblSecurity.SecurityID = Tbl_Image.SecurityID"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
'The commented line is for no images, the longer line adds an image based on customer's past orders
.Nodes.Add "TOP", tvwChild, "LO" & rs!UserID, rs!UserID 'Each is a child of TOP
.Nodes.Add "LO" & rs!UserID, tvwChild, "DU" & CLng(Rnd() * 2000000000) 'These are dummies
rs.MoveNext
Loop
End If
Set oNode = .Nodes("TOP").Child 'Make sure we can see the first level
oNode.EnsureVisible
End With
End Sub
Private Sub oTV_Expand(ByVal Node As MSComctlLib.Node)
Dim oNode As Node
Dim oNewNode As Node
Dim rs As DAO.Recordset
Dim SQL As String
Set oNode = Node.Child
If Left$(oNode.Key, 2) <> "DU" Then Exit Sub 'Only expand if it hasn't been done before
oTV.Nodes.Remove oNode.Key 'Get rid of the dummy node
Select Case Left$(Node.Key, 2)
Case "LO" 'If it's a User, add Date
SQL = "Select DISTINCT [UserID], [DateImported] FROM tblSecurity INNER JOIN Tbl_Image ON tblSecurity.SecurityID = Tbl_Image.SecurityID " & _
"Where UserID= """ & Mid$(Node.Key, 3) & """ " & _
"Order by [DateImported]"
Debug.Print "UserID= " & Mid$(Node.Key, 3)
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
Set oNewNode = oTV.Nodes.Add(Node, tvwChild, "LN" & " " & rs!UserID & " " & rs!DateImported, rs!DateImported)
oTV.Nodes.Add oNewNode, tvwChild, "DU" & CLng(Rnd() * 2000000000) 'Add a dummy under each customer
rs.MoveNext
Loop
End If
Case "LN" 'If it's a Date, add Image Title
SQL = "Select [FileName], [DateImported], [UserID] FROM tblSecurity INNER JOIN Tbl_Image ON tblSecurity.SecurityID = Tbl_Image.SecurityID " & _
"Where UserID= """ & Node.Parent.Text & """ And DateImported= #" & Format(CDate(Node.Text), "mm/dd/yyyy") & "# " & _
"Order by [FileName]"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
Debug.Print rs!FileName
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
Set oNewNode = oTV.Nodes.Add(Node, tvwChild, "TA" & " " & rs!DateImported & " " & rs!FileName, rs!FileName)
rs.MoveNext
Loop
End If
End Select
Debug.Print "DateImported= " & Node.Text
Debug.Print "UserID= " & Node.Parent.Text
Set rs = Nothing
Set oNode = Nothing
Set oNewNode = Nothing
End Sub
Private Sub oTV_NodeClick(ByVal Node As MSComctlLib.Node)
Dim sType As String
Dim nde As Node
Dim SQL As String
Dim strImagePath As String
Dim strImageID As Integer
Dim db As Database
Dim rst As Recordset
Dim SQL1 As String
Dim rst1 As Recordset
If Node.Key = "TOP" Then Exit Sub 'Don't do anything for TOP
Me.ImgCtrl.Visible = False 'Subform is invisible for now
Set nde = Node 'Put node in a variable
Do Until nde.Key = "TOP" 'Loop until we get back to the top
sType = Left$(nde.Key, 2)
Select Case sType
Case "TA" 'If it's a Tag #, set the subform's record source
SQL = "SELECT [FileName], [ImageID] FROM Tbl_Image " & _
"Where FileName='" & CStr(nde.Text) & "' "
Debug.Print "FileName =" & CStr(nde.Text)
'Debug.Print "ImagePath =" & ImagePath
strImagePath = DLookup("ImagePath", "Tbl_Image", "FileName=""" & CStr(nde.Text) & """") & "\" & CStr(nde.Text) & "." & DLookup("Ext", "Tbl_Image", "FileName=""" & CStr(nde.Text) & """")
Me.txtImagePath = strImagePath
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From [Tbl_Image] Where FileName = """ & CStr(nde.Text) & """", dbOpenDynaset)
With rst
strImageID = ![ImageID]
End With
Me.txtImageID = strImageID
Me.ImgCtrl.Picture = strImagePath
Me.ImgCtrl.Visible = True 'and make sure the image is visible
ImgCtrl.SizeMode = getBestFit(ImgCtrl)
Me.RecordSource = "Select * From Tbl_Image Where FileName = """ & CStr(nde.Text) & """ "
Me.FileName.ControlSource = "FileName"
Me.ImageName.ControlSource = "ImageTitle"
Me.Description.ControlSource = "Description"
Me.Status.ControlSource = "Status"
End Select
Set nde = nde.Parent 'Now work with the parent
Loop
Set db = CurrentDb
Set rst1 = db.OpenRecordset("Tbl_History", dbOpenDynaset)
DoCmd.SetWarnings False
SQL1 = "DELETE Tbl_History.* FROM Tbl_History"
db.Execute SQL1, dbFailOnError
DoCmd.SetWarnings True
With rst1
.AddNew
![OldFileName] = Me.FileName
.Update
End With
rst.Close
Me.OldFileName = DLookup("OldFileName", "Tbl_History")
End Sub
Option Compare Database
Option Explicit
Private WithEvents oTV As TreeView
Private Sub Form_Load()
Randomize Timer 'So we get a different set of numbers each time
Set oTV = Me.TV.Object 'The key to everything is to get to the
'TreeView object, not to its OLE container
CreateTV
End Sub
Private Sub cmdCollapse_Click()
CreateTV 'Recreate the Treeview from scratch, which means
'that new values will automatically appear
Me.ImgCtrl.Picture = ""
Me.ImgCtrl.Visible = True 'and make sure the image is visible
Me.RecordSource = ""
Me.FileName.ControlSource = ""
Me.ImageName.ControlSource = ""
Me.Description.ControlSource = ""
Me.Status.ControlSource = ""
Me.txtImageID = Null
End Sub
Private Sub CreateTV()
Dim oNode As Node
Dim oNewNode As Node
Dim i As Integer
Dim rs As DAO.Recordset
Dim SQL As String
With oTV
.Nodes.Clear 'Get rid of any existing nodes
.Nodes.Add , , "TOP", "User - Date Imported - Images" 'Create a known key for the top level
'SQL = "Select DISTINCT UserID from Tbl_Image Order By Date"
SQL = "SELECT DISTINCT UserID FROM tblSecurity INNER JOIN Tbl_Image ON tblSecurity.SecurityID = Tbl_Image.SecurityID"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
'The commented line is for no images, the longer line adds an image based on customer's past orders
.Nodes.Add "TOP", tvwChild, "LO" & rs!UserID, rs!UserID 'Each is a child of TOP
.Nodes.Add "LO" & rs!UserID, tvwChild, "DU" & CLng(Rnd() * 2000000000) 'These are dummies
rs.MoveNext
Loop
End If
Set oNode = .Nodes("TOP").Child 'Make sure we can see the first level
oNode.EnsureVisible
End With
End Sub
Private Sub oTV_Expand(ByVal Node As MSComctlLib.Node)
Dim oNode As Node
Dim oNewNode As Node
Dim rs As DAO.Recordset
Dim SQL As String
Set oNode = Node.Child
If Left$(oNode.Key, 2) <> "DU" Then Exit Sub 'Only expand if it hasn't been done before
oTV.Nodes.Remove oNode.Key 'Get rid of the dummy node
Select Case Left$(Node.Key, 2)
Case "LO" 'If it's a User, add Date
SQL = "Select DISTINCT [UserID], [DateImported] FROM tblSecurity INNER JOIN Tbl_Image ON tblSecurity.SecurityID = Tbl_Image.SecurityID " & _
"Where UserID= """ & Mid$(Node.Key, 3) & """ " & _
"Order by [DateImported]"
Debug.Print "UserID= " & Mid$(Node.Key, 3)
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
Set oNewNode = oTV.Nodes.Add(Node, tvwChild, "LN" & " " & rs!UserID & " " & rs!DateImported, rs!DateImported)
oTV.Nodes.Add oNewNode, tvwChild, "DU" & CLng(Rnd() * 2000000000) 'Add a dummy under each customer
rs.MoveNext
Loop
End If
Case "LN" 'If it's a Date, add Image Title
SQL = "Select [FileName], [DateImported], [UserID] FROM tblSecurity INNER JOIN Tbl_Image ON tblSecurity.SecurityID = Tbl_Image.SecurityID " & _
"Where UserID= """ & Node.Parent.Text & """ And DateImported= #" & Format(CDate(Node.Text), "mm/dd/yyyy") & "# " & _
"Order by [FileName]"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
Debug.Print rs!FileName
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
Set oNewNode = oTV.Nodes.Add(Node, tvwChild, "TA" & " " & rs!DateImported & " " & rs!FileName, rs!FileName)
rs.MoveNext
Loop
End If
End Select
Debug.Print "DateImported= " & Node.Text
Debug.Print "UserID= " & Node.Parent.Text
Set rs = Nothing
Set oNode = Nothing
Set oNewNode = Nothing
End Sub
Private Sub oTV_NodeClick(ByVal Node As MSComctlLib.Node)
Dim sType As String
Dim nde As Node
Dim SQL As String
Dim strImagePath As String
Dim strImageID As Integer
Dim db As Database
Dim rst As Recordset
Dim SQL1 As String
Dim rst1 As Recordset
If Node.Key = "TOP" Then Exit Sub 'Don't do anything for TOP
Me.ImgCtrl.Visible = False 'Subform is invisible for now
Set nde = Node 'Put node in a variable
Do Until nde.Key = "TOP" 'Loop until we get back to the top
sType = Left$(nde.Key, 2)
Select Case sType
Case "TA" 'If it's a Tag #, set the subform's record source
SQL = "SELECT [FileName], [ImageID] FROM Tbl_Image " & _
"Where FileName='" & CStr(nde.Text) & "' "
Debug.Print "FileName =" & CStr(nde.Text)
'Debug.Print "ImagePath =" & ImagePath
strImagePath = DLookup("ImagePath", "Tbl_Image", "FileName=""" & CStr(nde.Text) & """") & "\" & CStr(nde.Text) & "." & DLookup("Ext", "Tbl_Image", "FileName=""" & CStr(nde.Text) & """")
Me.txtImagePath = strImagePath
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From [Tbl_Image] Where FileName = """ & CStr(nde.Text) & """", dbOpenDynaset)
With rst
strImageID = ![ImageID]
End With
Me.txtImageID = strImageID
Me.ImgCtrl.Picture = strImagePath
Me.ImgCtrl.Visible = True 'and make sure the image is visible
ImgCtrl.SizeMode = getBestFit(ImgCtrl)
Me.RecordSource = "Select * From Tbl_Image Where FileName = """ & CStr(nde.Text) & """ "
Me.FileName.ControlSource = "FileName"
Me.ImageName.ControlSource = "ImageTitle"
Me.Description.ControlSource = "Description"
Me.Status.ControlSource = "Status"
End Select
Set nde = nde.Parent 'Now work with the parent
Loop
Set db = CurrentDb
Set rst1 = db.OpenRecordset("Tbl_History", dbOpenDynaset)
DoCmd.SetWarnings False
SQL1 = "DELETE Tbl_History.* FROM Tbl_History"
db.Execute SQL1, dbFailOnError
DoCmd.SetWarnings True
With rst1
.AddNew
![OldFileName] = Me.FileName
.Update
End With
rst.Close
Me.OldFileName = DLookup("OldFileName", "Tbl_History")
End Sub