Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: Query to display all related data from 4-level tree

    Hello all. I have uploaded the Asset tracking database provided by Microsoft. I am trying to modify it a bit to meet our needs and need some assistance.

    The main form Assets has a combobox UnitID with a rowsource of Query1 which looks like this.

    Code:
    SELECT Units.Unit AS Selected, Units_1.Unit AS Parent, Units_2.Unit AS Items
    FROM (Units INNER JOIN Units AS Units_1 ON Units.ParentID=Units_1.UnitID) INNER JOIN Units AS Units_2 ON Units_1.UnitID=Units_2.ParentID
    WHERE (((Units.Unit)=Forms!Assets!UnitID));
    There are 3 other textboxes, Sector, District, Area, that I need to display associated data from the selected UnitID. The Units table contains every unit that could be selected with the ParentID column identifying the Parent-Child relationships of each by designating the next level above the selected Unit.

    UnitID Unit ParentID
    1 Headquarters 0
    2 Atlantic 1
    3 Midwest 1
    4 Pacific 1
    5 Upper 2
    6 Lower 2
    7 Upper 3
    8 Lower 3
    9 Upper 4
    10 Lower 4
    11 New York 5
    12 Florida 6
    13 New York City 11
    14 Miami 12
    15 DC 6

    For example, if I selected Miami from the Forms!Assets!UnitID combobox, I need Florida to display in the Forms!Assets!Sector textbox, Lower to display in the Forms!Assets!District textbox, and Atlantic to display in the Forms!Assets!Area textbox.

    Additionally, as you can see, not every selection has 4 levels in the tree. If I select DC, I need Lower to display in the Forms!Assets!District textbox, and Atlantic to display in the Forms!Assets!Area textbox leaving the Forms!Assets!Sector textbox blank. If I select Headquarters, all teh textboxes should be blank.

    I hope someone is up to the challenge because I could really use some assistance with this.

    Thanks in advance,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 10-23-09 at 11:03.

  2. #2
    Join Date
    May 2009
    Posts
    48
    Trying something different that logically speaking should work, but I receive the following error for the first line of code:

    "Runtime error "2113"
    The value you entered isn't valid for this field."

    What I need the code below to do is take the Forms!Assets!Unit_ID value from the combobox, and in the afterupdate event, compare it to the UnitID field on the Units table, take the associated value from the ParentID field, and display the unit associated with the ParentID value.

    Code:
    Private Sub Unit_ID_AfterUpdate()
        Forms!Assets!Sector = DLookup("[Unit]", "Units", "ParentID = " & Forms!Assets![Unit_ID])
        Forms!Assets!District = DLookup("[Unit]", "Units", "ParentID = " & Forms!Assets![Sector])
        Forms!Assets!Area = DLookup("[Unit]", "Units", "ParentID= " & Forms!Assets![District])
    End Sub
    The field that causes the error is the first line of code, due to

    Code:
    Forms!Assets!Sector
    being assigned NULL when the line is complete.

    When reviewing the values on the code break, there is a correct value assigned to [Unit_ID]) at the end of the first line, but this is not getting saved to the

    Code:
    Forms!Assets!Sector
    field.

    Please see the attached test file. I am still hopefull that someone can assist me.

    Thanks,
    Andrew
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •