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.
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
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.
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.