TreeView Query and or Queries Question

[Russ via AccessMonster.com]

Trying to use treeview (not sure how many queries I need Or how to accomplish
this)
My goal is to filter a datasheet form based on my tblAssets (AssetID,
LocationID, ClassificationID, CategoryID, SubCategoryID) next to the treeviw
by each selection from the treeview all the way down to the specific i.d.
number. Any help with treeview query/queries set-up would be Greatly
Appreciated

I click on "Asset Locations, it opens up listing the three locations in my
tblLocation (LocationID, LocationName)

I then click on Asset Main Classifications, it opens up listing all
Classifications in my tblAssetClassifications(ClassificationID,
ClassificationName)

I then click on Asset Main Categories, it opens up showing all categories in
my tblAssetCategories(CategoryID, CategoryName)

I then click on Asset Main SubCategories, it opens up showing all
subcategories in my tblAssetSubCategories(SubCategoryID, SubCategoryName) I
now see all equipment listed by ID

example:
My Assets...
Asset Locations = Location1
Location2 (I select location 2, now show
Classifications)
Location3

Asset Main Classifications = Plant Equipment (I select Plant Equipment , now
show Categories)
Field Equipment
Production Equipment

Asset Categories = A/C Units (I select A/C Units , now show
SubCategories)
Furnace

Asset Sub-Categories = Window Mounted
Main Unit (I select
Main Unit , now see all Assets by ID)

 

[Russ via AccessMonster.com]

Can any offer some advise / guidance on setting this up, seems to more
difficult then I thought?

 

[Rob Parker]

Hi Russ,

Here's a selection of articles regarding the treeview control:

The best example I've seen which illustrates the MS treeview control and its
capabilities is on Alex Dybenko's site, at
http://www.pointltd.com/Downloads/Details.asp?dlID=36

There's an article on using the MS Treeview control in one of Helen
Fedemma's Access Archon columns: http://www.helenfeddema.com/access.htm - on
the Archon page (which opens from that link) download accarch52.zip.
There's also an article on using the MS Treeview wizard (formerly available
as a separate download for A87 - not sure if it's stilll available, or works
with later versions) in accarch53.zip. And, perhaps most applicable to your
current situation, an article on using a treeview control as a record
selector, at accarch103.zip.

For more references, see this page on Jeff Conrad's Access Junkie site:
http://www.accessmvp.com/JConrad/accessjunkie/treeview.html

HTH,

Rob

 

[Russ via AccessMonster.com]

Thank for the links and info Rob,
But having problem getting it to work, maybe someone can offer some help with
the code I am using...
The problem I am having is it will only fill in the Classifications for
location 1, not location 2 or 3
Need it like this...
'Example:
Locations
Location 1 (Long Beach)
Classification 1 (Plant Equipment)
Classification 2 (Production Equipment)
Location 2 (Texas)
Classification 1 (Plant Equipment)
Classification 2 (Production Equipment)

Option Compare Database
Private db As DAO.Database
Private rstEmployees As DAO.Recordset
Private rstAssets As DAO.Recordset
Private rstLocations As DAO.Recordset
Private rstAssetClassification As DAO.Recordset
Private rstAssetCategory As DAO.Recordset
Private rstAssetSubCategory As DAO.Recordset

'NodeIDs in the treeview collection must be alphabetic.
'Nodes-Unique Key Ledger
'MEN = Menu
'ASSETS = Assets
'LOC = Asset Location
'ACL = Asset Classification
'ACA = Assets Category
'ASC = Assets Sub-Category
'==================================================================
'This procedure populates the TreeView control when the form opens.
'==================================================================
Private Sub Form_Load()

On Error GoTo ErrForm_Load

'Add Statusbar Text...
SysCmd acSysCmdSetStatus, "Setting up treeview"

Set db = CurrentDb

'===============================================================
'Start To Set-Up Our Data, we will need the following information...
'=> Assets (tblAssets)
'=> Asset Classification (tblAssetClassification)
'=> Asset Category (tblAssetCategories)
'=> Asset Sub-Category (tblAssetSubCategories)
'===============================================================
'Open the tblAssets table.
Set rstAssets = db.OpenRecordset("Select * From tblAssets", dbOpenDynaset)


'Open the tblAssets table.
Set rstLocations = db.OpenRecordset("Select * From tblLocations",
dbOpenDynaset)

'Open the tblAssetClassification table.
Set rstAssetClassification = db.OpenRecordset("tblAssetClassification",
dbOpenDynaset, dbReadOnly)

'Open the tblAssetCategories table.
Set rstAssetCategory = db.OpenRecordset("Select * From tblAssetCategories
Order by CategoryName", dbOpenDynaset)

'Open the tblAssetSubCategories table.
Set rstAssetSubCategory = db.OpenRecordset("Select * From
tblAssetSubCategories Order by SubCategoryName", dbOpenDynaset)

'===============================================================
'End - Data Set-Up
'===============================================================

'Create a reference to the TreeView Control.
Set objTree = Me!TreeView1.Object

'Create a reference to the ImageList
Set objImage = Me.ImageList.Object

'link TreeView object to Imagelist object
Set objTree.ImageList = objImage

'===============================================================
'Add(Relative, Relationship, Key, Text, Image, SelectedImage)
Set nNode = objTree.Nodes.Add(, , "MEN", "Company Equipment Menu", "Home")
'Root/Parent
nNode.Bold = True 'Set the node text to BOLD
nNode.ForeColor = RGB(0, 0, 5) 'Set the node text Color to BLACK
nNode.BackColor = RGB(255, 255, 255) 'Set the node back Color to
WHITE
objTree.Nodes("MEN").Expanded = True

'=========================================================================
'Add(Relative, Relationship, Key, Text, Image, SelectedImage)
Set nNode = objTree.Nodes.Add("MEN", tvwChild, "ASS", "All Company
Equipment", "Mixer") 'key is Case sensitive
nNode.Bold = True 'Set the node text to BOLD
nNode.ForeColor = RGB(0, 0, 5) 'Set the node text Color to BLACK
nNode.BackColor = RGB(255, 255, 255) 'Set the node back Color to
WHITE
Set nNode = objTree.Nodes.Add("ASS", tvwChild, "LOC", "Locations",
"Location")

'===============================================================
' Set-Up Locations & Classifications
'Example:
'Locations
' Location 1 (Long Beach)
' Classification 1 (Plant Equipment)
' Classification 2 (Production Equipment)
' Location 2 (Texas)
' Classification 1 (Plant Equipment)
' Classification 2 (Production Equipment)
'===============================================================

Dim intIndex As Integer ' Variable for index.
Dim KeyIndex As String ' Variable for index.

Do Until rstLocations.EOF


'Extract the Classification's name.
strAssetLocationPointer = rstLocations![LocationID]
strAssetLocation = rstLocations![LocationName]

'Add a root level node to the tree for the supervisor.
'Add(Relative, Relationship, Key, Text, Image, SelectedImage)
Set nNode = objTree.Nodes.Add("LOC", tvwChild, "LOC" & CInt
(rstLocations!LocationID), strAssetLocation, "LocationPic")

intIndex = nNode.Index
KeyIndex = nNode.Key

'First, we must move through the Asset Classification table and
create a Node object for each Category in the table.
Do Until rstAssetClassification.EOF

'Extract the Classification's ID.
strAssetClassificationID = rstAssetClassification!
[AssetClassificationID]
'Extract the Classification's name.
strAssetClassification = rstAssetClassification![AssetClassification]

Set nNode = objTree.Nodes.Add(KeyIndex, tvwChild, "ACL" & CInt
(rstAssetClassification!AssetClassificationID), strAssetClassification,
"ClassPic")

rstAssetClassification.MoveNext

Loop

rstLocations.MoveNext

Loop

SysCmd acSysCmdSetStatus, "Ok, we are all set your treeview is ready to
use"

ExitForm_Load:
Exit Sub

ErrForm_Load:
MsgBox Err.Description, vbCritical, "Form_Load"
Resume ExitForm_Load
End Sub

Hi Russ,

Here's a selection of articles regarding the treeview control:

The best example I've seen which illustrates the MS treeview control and its
capabilities is on Alex Dybenko's site, at
http://www.pointltd.com/Downloads/Details.asp?dlID=36

There's an article on using the MS Treeview control in one of Helen
Fedemma's Access Archon columns: http://www.helenfeddema.com/access.htm - on
the Archon page (which opens from that link) download accarch52.zip.
There's also an article on using the MS Treeview wizard (formerly available
as a separate download for A87 - not sure if it's stilll available, or works
with later versions) in accarch53.zip. And, perhaps most applicable to your
current situation, an article on using a treeview control as a record
selector, at accarch103.zip.

For more references, see this page on Jeff Conrad's Access Junkie site:
http://www.accessmvp.com/JConrad/accessjunkie/treeview.html

HTH,

Rob

Can any offer some advise / guidance on setting this up, seems to more
difficult then I thought?

[quoted text clipped - 41 lines]

 

[Rob Parker]

Hi Russ,

I set up a quick-and-dirty sample and fairly quickly found the initial
problem. Your inner loop, which is populating the Classification section of
the tree, is using rstAssetClassification. This recordset reaches the end,
and must be reset to the start before this loop is run again for the next
Location. So, you need to insert an additional line to move to the start of
the recordset before the innner Do loop, thus:
...
rstAssetClassification.MoveFirst
Do Until rstAssetClassification.EOF
...

This creates an additional problem, because the Key value you are assigning
is based only on the classification and so you will get a "Key is not unique
in collection" error. You need to set the Key in the inner loop to a
compound key consisting of both the classification and the location - and
since you've already stored the LOC key in the KeyIndex variable in the
outer loop, this is easy; just concatenate KeyIndex to the ACL key, thus:
...
Set nNode = objTree.Nodes.Add(KeyIndex, tvwChild, "ACL" &
CInt(rstAssetClassification!AssetClassificationID) & keyIndex,
strAssetClassification, "ClassPic")
...

HTH,

Rob

Thank for the links and info Rob,
But having problem getting it to work, maybe someone can offer some
help with the code I am using...
The problem I am having is it will only fill in the Classifications
for location 1, not location 2 or 3
Need it like this...
'Example:
Locations
Location 1 (Long Beach)
Classification 1 (Plant Equipment)
Classification 2 (Production Equipment)
Location 2 (Texas)
Classification 1 (Plant Equipment)
Classification 2 (Production Equipment)

Option Compare Database
Private db As DAO.Database
Private rstEmployees As DAO.Recordset
Private rstAssets As DAO.Recordset
Private rstLocations As DAO.Recordset
Private rstAssetClassification As DAO.Recordset
Private rstAssetCategory As DAO.Recordset
Private rstAssetSubCategory As DAO.Recordset

'NodeIDs in the treeview collection must be alphabetic.
'Nodes-Unique Key Ledger
'MEN = Menu
'ASSETS = Assets
'LOC = Asset Location
'ACL = Asset Classification
'ACA = Assets Category
'ASC = Assets Sub-Category
'==================================================================
'This procedure populates the TreeView control when the form opens.
'==================================================================
Private Sub Form_Load()

On Error GoTo ErrForm_Load

'Add Statusbar Text...
SysCmd acSysCmdSetStatus, "Setting up treeview"

Set db = CurrentDb

'===============================================================
'Start To Set-Up Our Data, we will need the following
information... '=> Assets (tblAssets)
'=> Asset Classification (tblAssetClassification)
'=> Asset Category (tblAssetCategories)
'=> Asset Sub-Category (tblAssetSubCategories)
'===============================================================
'Open the tblAssets table.
Set rstAssets = db.OpenRecordset("Select * From tblAssets",
dbOpenDynaset)


'Open the tblAssets table.
Set rstLocations = db.OpenRecordset("Select * From tblLocations",
dbOpenDynaset)

'Open the tblAssetClassification table.
Set rstAssetClassification =
db.OpenRecordset("tblAssetClassification", dbOpenDynaset, dbReadOnly)

'Open the tblAssetCategories table.
Set rstAssetCategory = db.OpenRecordset("Select * From
tblAssetCategories Order by CategoryName", dbOpenDynaset)

'Open the tblAssetSubCategories table.
Set rstAssetSubCategory = db.OpenRecordset("Select * From
tblAssetSubCategories Order by SubCategoryName", dbOpenDynaset)

'===============================================================
'End - Data Set-Up
'===============================================================

'Create a reference to the TreeView Control.
Set objTree = Me!TreeView1.Object

'Create a reference to the ImageList
Set objImage = Me.ImageList.Object

'link TreeView object to Imagelist object
Set objTree.ImageList = objImage

'===============================================================
'Add(Relative, Relationship, Key, Text, Image, SelectedImage)
Set nNode = objTree.Nodes.Add(, , "MEN", "Company Equipment Menu",
"Home") 'Root/Parent
nNode.Bold = True 'Set the node text to BOLD
nNode.ForeColor = RGB(0, 0, 5) 'Set the node text Color to
BLACK nNode.BackColor = RGB(255, 255, 255) 'Set the node back
Color to
WHITE
objTree.Nodes("MEN").Expanded = True

'=========================================================================
'Add(Relative, Relationship, Key, Text, Image, SelectedImage)
Set nNode = objTree.Nodes.Add("MEN", tvwChild, "ASS", "All Company
Equipment", "Mixer") 'key is Case sensitive
nNode.Bold = True 'Set the node text to BOLD
nNode.ForeColor = RGB(0, 0, 5) 'Set the node text Color to
BLACK nNode.BackColor = RGB(255, 255, 255) 'Set the node back
Color to
WHITE
Set nNode = objTree.Nodes.Add("ASS", tvwChild, "LOC", "Locations",
"Location")

'===============================================================
' Set-Up Locations & Classifications
'Example:
'Locations
' Location 1 (Long Beach)
' Classification 1 (Plant Equipment)
' Classification 2 (Production Equipment)
' Location 2 (Texas)
' Classification 1 (Plant Equipment)
' Classification 2 (Production Equipment)
'===============================================================

Dim intIndex As Integer ' Variable for index.
Dim KeyIndex As String ' Variable for index.

Do Until rstLocations.EOF


'Extract the Classification's name.
strAssetLocationPointer = rstLocations![LocationID]
strAssetLocation = rstLocations![LocationName]

'Add a root level node to the tree for the supervisor.
'Add(Relative, Relationship, Key, Text, Image, SelectedImage)
Set nNode = objTree.Nodes.Add("LOC", tvwChild, "LOC" & CInt
(rstLocations!LocationID), strAssetLocation, "LocationPic")

intIndex = nNode.Index
KeyIndex = nNode.Key

'First, we must move through the Asset Classification table and
create a Node object for each Category in the table.
Do Until rstAssetClassification.EOF

'Extract the Classification's ID.
strAssetClassificationID = rstAssetClassification!
[AssetClassificationID]
'Extract the Classification's name.
strAssetClassification =
rstAssetClassification![AssetClassification]

Set nNode = objTree.Nodes.Add(KeyIndex, tvwChild, "ACL" & CInt
(rstAssetClassification!AssetClassificationID),
strAssetClassification, "ClassPic")

rstAssetClassification.MoveNext

Loop

rstLocations.MoveNext

Loop

SysCmd acSysCmdSetStatus, "Ok, we are all set your treeview is
ready to use"

ExitForm_Load:
Exit Sub

ErrForm_Load:
MsgBox Err.Description, vbCritical, "Form_Load"
Resume ExitForm_Load
End Sub

Hi Russ,

Here's a selection of articles regarding the treeview control:

The best example I've seen which illustrates the MS treeview control
and its capabilities is on Alex Dybenko's site, at
http://www.pointltd.com/Downloads/Details.asp?dlID=36

There's an article on using the MS Treeview control in one of Helen
Fedemma's Access Archon columns:
http://www.helenfeddema.com/access.htm - on the Archon page (which
opens from that link) download accarch52.zip. There's also an
article on using the MS Treeview wizard (formerly available as a
separate download for A87 - not sure if it's stilll available, or
works with later versions) in accarch53.zip. And, perhaps most
applicable to your current situation, an article on using a treeview
control as a record selector, at accarch103.zip.

For more references, see this page on Jeff Conrad's Access Junkie
site: http://www.accessmvp.com/JConrad/accessjunkie/treeview.html

HTH,

Rob

Can any offer some advise / guidance on setting this up, seems to
more difficult then I thought?

[quoted text clipped - 41 lines]

Main Unit (I
select Main Unit , now see all Assets by ID)