Unconfigured Ad Widget



No announcement yet.

Displaying Two Different Tables Data In One Treeview

  • Filter
  • Time
  • Show
Clear All
new posts

  • Displaying Two Different Tables Data In One Treeview

    Is there anyway to fill a Treeview with information from one access column in a table and then have a branch off that which displays data from another column in a separate table? I can display data in my treeview but only if the data is from two columns in the same table. Please Help!!

    This is my current code (see Bellow). The line of code ( Set rs = db.OpenRecordset("SELECT DISTINCT [user name] FROM custodian WHERE Name='" & rs2("Name") & "'")) which is highlighted by this *** is where it accesses the information from the 'Custodian' table. Its is currently getting the 'user name' and 'name' columns. i would like it to get the User Name from that table but branching off from that i would like the 'Asset Name' column from the 'asset master' table. I no its very complicated, any help would be very much appreciated. ive attached a picture of what the treeview table looks like at the moment. hopefully i have explained everything in great enough detail. Thank you in advance.

    Option Compare Database
    Option Explicit

    Private Sub Custodian_history_form_Load()

    With Me.[Custodian History Form]
    .Style = 6
    .LineStyle = tvwRootLines
    .Indentation = 240
    .Appearance = ccFlat
    .HideSelection = False
    .BorderStyle = ccFixedSingle
    .HotTracking = True
    .FullRowSelect = True
    .Checkboxes = False
    .SingleSel = False
    .Sorted = False
    .Scroll = True
    .LabelEdit = tvwManual
    .Font.Name = "Verdana"
    .Font.Size = 9
    End With

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strQuery1 As String
    Dim nod As Object
    Dim nodChild As Object
    Dim strNode1Text As String
    Dim strVisibleText As String
    Dim strMessage As String
    Dim intVBMsg As Integer
    Dim intCounter As Integer
    Dim stringcount As String
    Dim strQuery2 As String
    Dim rs2 As DAO.Recordset
    Dim strNode2Text As String
    Dim db2 As DAO.Database
    Dim rs3 As DAO.Recordset

    Me.[Custodian History Form].Nodes.Add Text:="Custodian History", Key:="Custodianhistory987"
    strQuery2 = "select DISTINCT Name from [Custodian]"
    Set db = CurrentDb
    Set rs2 = db.OpenRecordset(strQuery2)
    With Me.[Custodian History Form]
    Do Until rs2.EOF
    strNode2Text = StrConv("Level1" & rs2!Name, vbLowerCase)
    strVisibleText = rs2!Name

    'if model name is null, will have problem
    If IsNull(rs2!Name) Then
    MsgBox "number is null"
    strVisibleText = "no name"
    End If
    stringcount = CStr(intCounter)
    strNode2Text = strNode2Text + stringcount
    Set nod = Me.[Custodian History Form].Nodes.Add(Relative:="Custodianhistory987", relationship:=tvwChild, Key:=strNode2Text, Text:=strVisibleText)

    'Now add the child root for this Asset Name
    ***Set rs = db.OpenRecordset("SELECT DISTINCT [user name] FROM custodian WHERE Name='" & rs2("Name") & "'")
    Do Until rs.EOF
    Set nodChild = .Nodes.Add(Relative:=strNode2Text, relationship:=tvwChild, Text:=rs("user name"))
    nodChild.Expanded = False


    End With
    Set rs2 = Nothing
    Set db = Nothing
    End Sub