Solved Parent category should show records of all subcategories - ms access tree view

[Ihk]

I have tree view which shows categories and sub of subcategories. Which I would name it as "Product groups"
- Main form name (having tree view of product group) : ProductGroupF
- Subform name : ProductDetailSubF (bound to query "ProductDetailQ")

Two tables:
1st table -- "ProductGroupT" looks like as below

as can be seen subgroup is created, by bringing groupID into parentID
2) 2nd table -- "ProductDetailT" Looks like as below

in this table product group ID (not parent ID) is inserted, while creating a product detail.
ProductDetailQ is query combining above two

Product groups as tree on form look like as below

In this it can be seen "Groceries" is one of the main Group, then Rice + Flour are sub at level 2, and so on...
Main form has an "Unbound control" named as "txtGroupID", by click on any of tree Nod (groups), this field is populated with ID number via vba.
this "txtGroupID" is acting as master field on main form, which is linked with subform child field. If this "txtGroupID" value is "4" which is Men in above group table so all products from Product detail table matching to "4" are filtered.
Because main form and subform have Master and child fields linked to filter exact match of IDs, therefore now I am not using this relationship. Rather I am using Sql select statement as below.
Reason: As can be seen in product group table "Product Group" has Group ID: 1, while parent ID: 0, which are not used in any product. So by clicking on top of tree "Product Group", it does not show any product. So subform is empty.
While I wanted to have all products in this case. Therefore sql select statement with condtion if greater than 1, is working fine to deliver this.

Now my question:
in above tree Groceries, rice, Flour have no product but their further subs eg, Basmati rice etc have product. My code does not show all products under Groceries while I want it should show. By click on Groceries, all products related to groceries should appear.
Similarly if we click on "Flour" it should show all types of Flour, and then further filtering by clicking on it sub levles.
I hope I am able to make you understand what I mean. below is my code on Nod click.

On main form load "to show all records, product detail" by default --- on load event

Forms.ProductGroupF.ProductDetailSubF.Form.RecordSource = "SELECT * FROM ProductDetailQ "

Main form has two unbound controls (1. txtGroupID 2. txtParentID ), which are populated by nod click
on Nod click event

Private Sub CategoriesInTreeView_NodeClick(ByVal Node As Object)
Dim objName As String, nodOn As MSComctlLib.Node
Dim PID As Integer

'This applies only to the Node having child Nodes

If Node.Expanded = False Then
    Node.Expanded = True
Else
    Node.Expanded = False
End If

' this is for bringing the ID on the Main form, this ID field which is linked with subform as Master field

Me.txtGroupID = Mid(Node.Key, 2)
'If 1st parent category is selected, whose ID is Zero, then bring all results

If Me.txtParentID.Value = 0 Then
Forms.ProductGroupF.ProductDetailSubF.Form.RecordSource = "SELECT * FROM ProductDetailQ "
Else
'If ID is greater than zero, then brings results as per Nod Click (based on their ID)

If Me.txtParentID.Value > 0 Then
Forms.ProductGroupF.ProductDetailSubF.Form.RecordSource = "SELECT * FROM ProductDetailQ " & _
      "WHERE  (ProductDetailQ.[ID_ProductGroup])= " & Me.txtGroupID.Value
End If
End If
' to find the parent ID of category, by click on NOD

PID = DLookup("[ID_ProdParentGroup]", "[ProductGroupT]", "[ID_ProductGroup] =" & Me.txtGroupID.Value)
Me.txtParentID.Value = PID

End Sub

Below I made illustration to show, how ProductGroup ID, is acting as ProdParentGroupID in next (down) sub level and so on.

Thanks for reading this. How can I modify my above code on NodeClick event.

 

[MajP]

Could your post your two tables? That would help.

Main Group 'Product Group
--- SubGroup 'Groceries
-------SubSubGroup 'Rice
----------- 3SubGroup ' Basmatic
----------------Product 'Ja Basmatic

1. Are the actual products always at the same level assigned to a 3SubGroup as per your example?
2. Are the amount of levels set? In other words you always go to the 3SubGroup and no more or less.

If either 1 or 2 is not true then this has to be done recursively. This can be done, but not easily.

In other words you if you clicked on Grocery you would have to span the tree recursively below Grocery and store the productids. Then return a query of the spanned set
....where ProductID in (Long list of numbers found from spanning the nodes)


Questions 1 and 2 are important because the solution is easy or pretty hard depending on your design.
Your example is not very illustrative since you only show 1 product (Ja Basmati Rice). Everything else is a Product Group.
Your design would allow products to be assigned at any level of the tree, and that would require a recursive query. Or if you can have various amount of levels that would require a recursive query.

 

[MajP]

One more thing. I would assign my keys differently. If the node is a ProductGroup then the Key should be some identifier plus the ID_ProductGroup
rice: PG6
If it is a product the key should have a different IDentifier and store the ID_ProductDetail
Ja Bamati Rice: PD17
Now you can determine if it is a Product or a Group.

So assuming rules 1 and 2 are false (Products at different levels, and various sub levels) you can now span the Nodes below a Parent node.
Click on Grocery and Span all the child nodes. If the child node identifier is PD then save the PK.

 

[Ihk]

One more thing. I would assign my keys differently. If the node is a ProductGroup then the Key should be some identifier plus the ID_ProductGroup
rice: PG6
If it is a product the key should have a different IDentifier and store the ID_ProductDetail
Ja Bamati Rice: PD17
Now you can determine if it is a Product or a Group.

So assuming rules 1 and 2 are false (Products at different levels, and various sub levels) you can now span the Nodes below a Parent node.
Click on Grocery and Span all the child nodes. If the child node identifier is PD then save the PK.

Thank you very much for taking time.
Attached is quick and dirty demo db. Its main 1st form will open automatically. Please ignore data appeared in subform (showing IDs fields).
In this demo db i have not created form to add groups and sub of sub groups, but yes users will be able to create as many as they want subsubsub etc. Also this demo does not contain form to change colours for each group level.
Probably this demo db will answer you all questions asked in your above post#2.
This is also nice idea to change Identifier as mentioned in post#3.
It will be helpful, if you make changes as per your approach. thank you.

 

[MajP]

See Demo

Public Function GetRecursiveChildRecords(GroupID_In As Long) As String
  Dim ChildGroups As String
  Dim StrSql As String
  ChildGroups = GetChildGroups(GroupID_In)
  StrSql = "Select * from ProductDetailQ WHERE ID_ProdParentGroup" & _
            " IN (" & ChildGroups & ") Order by 4,2"
  GetRecursiveChildRecords = StrSql
End Function

Public Function GetChildGroups(GroupID_In As Long, _
                               Optional Groups As String = "") As String
  Dim rs As DAO.Recordset
  Dim StrSql As String
  Dim CurrentID As Long
  StrSql = "Select Distinct ID_ProductGroup from ProductGroupT " & _
        "where ID_ProdParentGroup = " & GroupID_In
  Set rs = CurrentDb.OpenRecordset(StrSql)
  If Groups = "" Then
    Groups = GroupID_In
  Else
    Groups = Groups & ", " & GroupID_In
  End If
  Do While Not rs.EOF
    CurrentID = rs!ID_ProductGroup
     GetChildGroups CurrentID, Groups
    rs.MoveNext
  Loop
  GetChildGroups = Groups
End Function

If you Click on General Node 2 then you get

Select * from ProductDetailQ WHERE ID_ProdParentGroup IN (2, 4, 5, 17, 18, 19) Order by 4,2

 

[Ihk]

See Demo

Public Function GetRecursiveChildRecords(GroupID_In As Long) As String
  Dim ChildGroups As String
  Dim StrSql As String
  ChildGroups = GetChildGroups(GroupID_In)
  StrSql = "Select * from ProductDetailQ WHERE ID_ProdParentGroup" & _
            " IN (" & ChildGroups & ") Order by 4,2"
  GetRecursiveChildRecords = StrSql
End Function

Public Function GetChildGroups(GroupID_In As Long, _
                               Optional Groups As String = "") As String
  Dim rs As DAO.Recordset
  Dim StrSql As String
  Dim CurrentID As Long
  StrSql = "Select Distinct ID_ProductGroup from ProductGroupT " & _
        "where ID_ProdParentGroup = " & GroupID_In
  Set rs = CurrentDb.OpenRecordset(StrSql)
  If Groups = "" Then
    Groups = GroupID_In
  Else
    Groups = Groups & ", " & GroupID_In
  End If
  Do While Not rs.EOF
    CurrentID = rs!ID_ProductGroup
     GetChildGroups CurrentID, Groups
    rs.MoveNext
  Loop
  GetChildGroups = Groups
End Function

If you Click on General Node 2 then you get

Select * from ProductDetailQ WHERE ID_ProdParentGroup IN (2, 4, 5, 17, 18, 19) Order by 4,2

I am really grateful for your help. I am travelling, and will look all above tonight.

 

[Ihk]

See Demo

Public Function GetRecursiveChildRecords(GroupID_In As Long) As String
  Dim ChildGroups As String
  Dim StrSql As String
  ChildGroups = GetChildGroups(GroupID_In)
  StrSql = "Select * from ProductDetailQ WHERE ID_ProdParentGroup" & _
            " IN (" & ChildGroups & ") Order by 4,2"
  GetRecursiveChildRecords = StrSql
End Function

Public Function GetChildGroups(GroupID_In As Long, _
                               Optional Groups As String = "") As String
  Dim rs As DAO.Recordset
  Dim StrSql As String
  Dim CurrentID As Long
  StrSql = "Select Distinct ID_ProductGroup from ProductGroupT " & _
        "where ID_ProdParentGroup = " & GroupID_In
  Set rs = CurrentDb.OpenRecordset(StrSql)
  If Groups = "" Then
    Groups = GroupID_In
  Else
    Groups = Groups & ", " & GroupID_In
  End If
  Do While Not rs.EOF
    CurrentID = rs!ID_ProductGroup
     GetChildGroups CurrentID, Groups
    rs.MoveNext
  Loop
  GetChildGroups = Groups
End Function

If you Click on General Node 2 then you get

Select * from ProductDetailQ WHERE ID_ProdParentGroup IN (2, 4, 5, 17, 18, 19) Order by 4,2

I just checked. There is one problem, if we click on parent group , all sub data is shown in sub form. But to further narrow down, if I click on sub group of that parent group, (which had data for that sub group), data is missing.
Example:
I clicked on "General" one of the parent group.

As a result : In this picture under General, we can see Men, clothes, Jewelry.

But If I click on "Men" or Ladies or even further narrow down to Clothes, Jewelry..... data does not appear, in any case.

 

[MajP]

I will check it was working. Might have messed something up before posting. Any reason you do not want to show the products in the tree? Seems confusing since you do not know what groups are empty.

 

[Ihk]

I will check it was working. Might have messed something up before posting. Any reason you do not want to show the products in the tree? Seems confusing since you do not know what groups are empty.

Products in the tree?? I never saw something like this. May be looks good.

But in my plan is, There will be no product goups, except the Main "Product Group" which will have parent ID zero. Further all product groups or sub groups will be added by end users, what ever and how much do they want. Want to keep it totally flexible for user. This way it can be used for any type of product.
while user add product / product detail. At the same time can create new product group for that product or select from already existing product group (if they have created). By tree click product full detail will appear, and by double click on product detail, it can be modified on a form which will slide in (either from right or left).

 

[MajP]

Had the wrong field name. To me this makes more sense.

 

[MajP]

You may want to look at this class and the demos. I take tree views to the extreme and automate most of the functionality.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

You especially want to master the drag and drop. This is the easiest way for a user to create groups and subgroups by dragging a node and its children under another node.

 

[gemma-the-husky]

This may have been mentioned, but I would give your products a product code that produces the sorting effect you want. Alternatively allocate each product to a product group that produces the sorting effect you want, and add a sort order field to sort the products in the appropriate order Then this sort of stuff becomes easy.

 

[gemma-the-husky]

You may want to look at this class and the demos. I take tree views to the extreme and automate most of the functionality.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

You especially want to master the drag and drop. This is the easiest way for a user to create groups and subgroups by dragging a node and its children under another node.

Depending what you are doing you may want to think carefully about what users can do. You might not want a user messing up a carefully designed coding structure.

 

[MajP]

Depending what you are doing you may want to think carefully about what users can do. You might not want a user messing up a carefully designed coding structure.

What does that mean? Does not make any sense.

 

[gemma-the-husky]

What does that mean? Does not make any sense.

I mean if you have a multi-user system, you don't want Fred deciding to move a subcategory into a different part of the tree, then Jim deciding to move it back, and Sheila deciding to change the names, and move a load of items around to different subcategories. Someone needs to have overall control of how it's all set up in the first place.

I must say your tree view class/module sounded great though, and I will certainly take a look at it.

 

[MajP]

@gemma-the-husky that makes sense, but has nothing to do with the "coding structure". That would be pure data, "Category / Sub Category Structure".

 

[gemma-the-husky]

@MajP
I meant that if you provide a mechanism to let users change the structure by simply dragging and dropping, you need to be judicious about how this is used. It's similar to the errors and duplications that arise when you let users add new records and edit existing records indiscriminately.

I take it you would only provide this as a management function anyway.

 

[Ihk]

Thank you very much. @MajP

 

[MajP]

I take it you would only provide this as a management function anyway

That would be up to the user of the class module to determine how they implement it, but the ability to limit drag and drop is a built in feature. The class I use has a property to enable or disable drag and drop and is determined at initialization. So you can restrict that feature.
However, even if you limited the drag and drop to admins, the problem is bigger than that if using the tree view in a multi user environment and allow additions, deletions, and changes. For example even if no user has drag and drop and users are allowed to add nodes or delete nodes there is no way to refresh another users treeview. You would probably need some kind of timer event to check for changes in the tree and the underlying table. Then give the user an option to reload the tree. Although these issues are not unique to a tree view the big difference is that a refresh of the tree can be a lot more expensive than a requery of a form.