Treeview key problem 1

[Flippertje]

I've build a table where i have the following contents:

Groupname Nodename
Test
Test Dixons
Test Dynabite
Dixons Computers
Dixons Software
Dixons Else
Dynabite Computers
Dynabite Software
Dynabite Else

Both computerstores (Dixons and Dynabite) sell computers, software and other stuff. I want to create a treeview in which i can put this.

The problem i run in to is the problem how to fill this table with all the computerproducts, Softwareproducts and other stuff. How can the treeview know wich product belongs to which store???

Please help me out... i've been puzzling about this too long and my summer holidays will start soon.... i've got to crack this before that time!

my nodes addingline:

set nd= treeview0.nodes.add(nodboss, tvwChild, rst![TreeID] & strText, StrText)

Since Treeview is discussed in three forums.. i've posted this in three forums.

Very many thanks!!
Flippertje

 

[gol4]

I know this thread is a bit old but here goes

When loading the treeview child node you specific which node it should be loaded under by passing its key to the add method. Each node needs to have a unique key. Don't confuse key with index.

You add nodes using the add method
.add(Relative key, Relationship, Nodes unique Key, Text,image)
so code would look like this
add first node(root)

.add ,,"test","test"

since it is the first node no need to specify relatives
"Test" is unique so will work as a key for this node
Now add the next node (child)
rst.movenext 'go to next record

.add(rst!groupname, tvwChild, rst![nodename], rst!nodename)

rst!groupname would be "test"
we are adding as a child tvwChild
so node will appear under parents whos key is "Test"
now we need a unique key for this node
Rst![nodename] would be unique for the next 2 records Dixon and Dynabite, but this will error eventually we need a better unique key (probably available from your record set usually rst!index) if not something like left(rst!groupname,2) & rst!nodename would work here.

.add(left(rst!groupname,2), tvwChild,left(rst!groupname,2) & rst![nodename], rst!nodename)


anyway if you just go thru the recordset ensuring the key is unique for each node you add, you will be fine.

 

[Flippertje]

Thank you for answering!

I''m not that familair with the treeview... I think you're answer won't solve the problem...

What i'm after: example

Dixons
|-Computers
| |-Compaq
| | |-Pentium
| | | |- all kinds of models
| | |-Dualcore
| | | |- all kinds of models
| |-IBM
| | |-Pentium (etc etc etc)
| |-Other brands
Dynabite
|-Computers
| |-Compaq
| | |-Pentium
| | | |- all kinds of models
| | |-Dualcore
| | | |- all kinds of models
| |-IBM
| | |-Pentium (etc etc etc)

The problem is that i can't seem to tell the Treeview which models of Pentium belong under the Dixonstree and which under the Dynabite tree...

What will solve this? I really hope you can anwer this. My holidays start this friday !

MAAAAAAnnnnnyyy Thanks!

Flippertje

 

[gol4]

That depends on the design of your database not the tree view.

How can you tell from the data stored in your tables which models of Pentium the Dixons carry and which the Dynabite stock?

I don't have a good enough understanding of you data structure to answer the best way, but if you are using a some sort of a parent child structure then you may have to do so self joins or pluck the data via a recursive function.

If you put up a better description of you data structure you may have better insight

 

[MajP]

Like Gol4 said the key is to have the correct table structure. Once you have that I could talk you through the correct code to populate and add to the tree. From what I hear you say, my structure would be

tblStores
autoStoreID (store ID, Primary Key)
strStoreName (store name)
other fields unique to a store

tblProducts
autoProductID (product ID)
strProductName (name of product)
strProductType (Type of product "computer", "Software"..)
other fields unique to a product

joinTblStore_Products
intStoreID (foriegn key from refTblStores)
intProductID (foriegn key from refTblProducts)
other fields unique to a product in a store if exists such as quantity

This is a many to many (many stores with products, many products in different stores).

Now my tree view will be based on a query linking "joinTblStore_Products" with "tblProducts" and "tblStores" linked by "autoProductID" with "intProductID" and "autoStoreID" with "intStoreID".

qryStore_ProductType_Product
intStoreID (from joinTblStore_Products)
strStoreName (from tblStores)
intProdcutID (from joinTblStore_Products)
strProductTypeDesc (from tblProducts)
strProductName (from tblProducts)

sort the query by strStoreName, strProductTypeDesc, then strProductName.

Now that you have the correct normalized data structure here are the steps.

1) as the form loads populate the treeview from three recordsets based on the query. Basically the logic is something like this. There are two ways to populate lengthwise or spanwise. Here is the lengthwise logic. (Not as compact but may be easier to understand)

Build three recordsets
rsStores (based on tblStores sorted by store name returns each unique store name)
rsProductTypes (based on qryStore_ProductType_Product and returns each unique store product type combination for each store and product type sorted alphabetically)
rsProductData (based on query "rsStore_ProductType_Product" returns all products)

Do the following pseudocode.

(populate store nodes)
while not rsStores.eof
add a store node (key is store name)
move to next store in recordset
loop

(populate product type nodes under correct stores)
while not rsProductTypes.eof
get the product type and store name
add product node to correct store node. The name of the store from the recordset is the key of the node to add it to
(the node key is store Name concatanated with a special character and then Product Name)
(example "Dixons~Computers")
move to next store product type in recordset
loop

(populate products under correct product type and store)
while not rsProductData.eof
get product Name, Store, Type from recordset
add to tree under correct product type
(key is Store Name concatenated with special character concatenated with product name another special character and then product name)
move to next product in recordset
loop

Your keys should then be something like this when done

Dixons (key Dixons)
|- Computers (Key Dixons~Computers)
| |- Dell (Key Dixons~Computers~Dell)

Now you use some string parsing to work between the treeview node keys and under table keys.

Now to add to the tree view. Use the node click event. If I click on the above Dell node, I get return the key (Dixon~Computers). This tells me where to add to my tables. If you get this far this could be kind of tricky, because you could add from an existing product or a brand new product. This should be enough to get you started.

Once thing unique about treeview keys. They have to be strings and start with a nonnumeric character. Normally I store some concatenation of primary keys.

 

[ZmrAbdulla]

Here is one from MS
How to fill a Treeview control recursively in Access 2000

http://support.microsoft.com/kb/209891

may help you.

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[MajP]

Zameer,
I agree that is a good approach if the data can be designed in a self referencing table. Unfortunately since Product Types repeat within the Store nodes it makes it very difficult to store this data in a self referencing table. You can still do it breadthwise like a recursive call, but it is a little more complicated in this case then loading it lengthwise.
The recursive call is a very succinct approach, but it is real complicated for the average programmer to get their hands around, IMHO.

 

[MajP]

Not sure if you are still interested in doing this because your deadline is approaching, but I noticed a little complication. Your original post should a very simple data structure but your example was more complicated. Your example shows a Product has a Manufacturer (Compaq,IBM) and Product Category (dual Core, Pentium). In that case the product table would be more like.

tblProducts
autoProductID (product ID)
strProductName (name of product)
strProductType (Type of product "computer", "Software"..)
strManufacturer (Compaq, Dell, Microsoft, etc)
strCategory (Pentium, Dual Core)
other fields unique to a product

Now the question is does this structure also work for software and other products? If other products have less or additional node levels then this gets complicated.

 

[Flippertje]

Thank you very much for the answer! I have to park it till after i'm back. But reading this gives me hope!!!
The table is build like this:

store - computer - brand - type - model

I've build a union query wich creates a nodename column and a groupname column. In my approach there is no way of telling which record belongs to which store....

Many thanks and STARS for you!

 

[MajP]

If you are still interested when you get back, I can talk you through it. I have a lot of treeview code. However, you are going to have to get your table design correct first. Your table and your union query do not make sense. You are going to need a many to many table design since a store can have many products, but a product can be sold at many stores.

 

[Flippertje]

MajP,

you're approach worked like a charm!!
Many thanks for you're help on the subject. I have a better understanding of the treeview object because of you!

Flippertje!