Turns out I ported an earlier version that was much simpler - Before all the customer/orders data was in the db. Im porting this current version now and will post when ready.
Turns out I ported an earlier version that was much simpler - Before all the customer/orders data was in the db. Im porting this current version now and will post when ready.
Here is the ADODB version
There is one Folder that contains three Files in the Zip
1. TreeviewDemo.adp: MS 2007 ADP or AccessProject:
2. TreeviewDemo.bak: MSSQL 2012 Database backup File
3. qryToViews.sql: SQL file of the statements I used to convert the Access queries to Views
Not sure if I clicked every button and tested all the functionality but it seems to be working
Main Changes Needed
1. Replaced References from DAO to ADODB
2 Added a module DBFunctions which includes a set of functions for working with ADODB
3. Calls to Set mRecordSet = CurrentDb.OpenRecordset were replaced with
Set mRecordSet = DBFunctions.GetRecordSet
4. Calls to rs.FindFirst -> Replaced with rs.MoveFirst then rs.Find
5. Calls to re.NoMatch -> Replaced with rs.BOF
6. Calls to rs.FindNext -> Replaced with rs.Find
7. Bookmark Declaration changed to Variant
8. General Cleanup of table/field names - My DB is case sensitive so it barked at non-matching case
In most places I made changes I added a comment Line of ( 'FRL ) my initials so anyone could search on them.
Now I actually have to study the code so I know what I'm playing with.
Like to move from being a Plumber to a Designer
Have fun guys - Hope this helps anyone who plays with SQL in an ADP Project
Here is an update to above Attachment - Found some areas of I had not updated - specifically the 2nd TreeForm which loads customers and orders.
Made the ports and cleaned up the query module.
@MajP
Somethings not working right on the sort sequence #. The Level_ID is populating correctly but the sort order isn't. Thoughts just by looking at the output?
Thanks @MajP Going to use this for my next project which will be a Open Code Code Library, I use Total Visual SourceBook 2013 But never built one before , I should also be able to get the code looking like the VB editor,
Love the images that will make it look really cool Thanks
@MajP
Somethings not working right on the sort sequence #. The Level_ID is populating correctly but the sort order isn't. Thoughts just by looking at the output?
That wouldn't stop it sorting alphabetically as the 'number parts' like 3.1.2 are not actually numbers
One way is to sort using a derived number column where e.g. L3.11.2 becomes 31102, L3.1.3 becomes 30103, L3.2=>30200 etc
NOTE I haven't tried any of this code & there may be a better way of sorting already built in ???
@Gasman and @isladogs.
Neither recommendation would be relevant here because it is using the tree position of the nodes as a means to establish a sort order. The Tree allows drag and drop and the ability to move nodes up and down within a level. In order to maintain that defined sort the tree view has an index property. If you read a fully expanded tree from the top down, the indices are defined in that order regardless of their level The code uses the tree node order not any kind of query sorting. It then saves this order to the table on close. This way when you reload the tree it maintains the desired order.
Node '1
----- Node '2
------------Node '3
----- Node '4
Node '5
In this class we also added a feature to Auto Outline. It thens adds the outline numbering once you move things up and down in the tree. But the sort and the numbering are somewhat independent.
Also look at the Pairing table. At first I did not think this was necessary to store the 3 keys (motherID, fatherID, and PairingID), but it had a lot of advantages IMO.
I know its ages since we spoke about this but I came across a discussion about pairing in a stack overflow discussion directly relevant to the above.
How do you constrain a database so there are only unique pairs in a database table?
If Kirk is friends with Victoria if we store that as a partnership we should not store Victoria as a partnership with Kirk.
From the thread we know that for any pairing the combination of the sum and the absolute difference will be unique so you can create an index on two computed columns
we know that A + B = B + A and we know that for absolute values A-B = B-A
we can make a computed column C1 of A+B and we can make a computed column C2 of ABS(A-B)
and then a unique constraint on C1,C2
I have two tables; one for storing thing and one for storing the relationship between two thing objects. dbfiddle example Assume: AB == BA. Storing both would be redundant A != B. The relation...
dba.stackexchange.com
For genetics maybe doesn't need to be reversable as mother could always be in one field and father in other...
I'm thinking this would be really good in systems where there was children and the parents or guardians may be two males / two females or guardian 1 and guardian 2 - so they are paired but the gender order of the pairing could vary although always unique irrespective of order.. and the indepotent nature of the constraint would mean that you could generate a pairing table from the primary keys of the guardians as you go along.
I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to span something like a file directory with an unknown amount of levels and branches. Common in doing things like Family trees and working with assemblies sub assemblies or tasks and sub tasks. Tree Views are a good way to show these Hierarchies. Think of an outline with multiple levels.
Recursion
So here is a simple example to demo recursion. The procedure is told how many times to loop itself and the counter is incremented each time the procedure is called. It simply adds 10 to the starting value each time it calls itself. Recursion always needs a way to check if it should call itself again or stop (does not call itself). Recursion if often very inefficient and resource heavy, but for certain data structure it may be the only way. (Note the below demo could obviously be done without recursion).
Code:
Public Sub RecursiveAdd10(HowManyTimes As Long, Optional Start As Long = 0, Optional Counter As Long = 0)
If HowManyTimes = Counter Then
Debug.Print "Final " & Start
Exit Sub
End If
Debug.Print "Value " & Start & " Counter " & Counter
Start = Start + 10
Counter = Counter + 1
RecursiveAdd10 HowManyTimes, Start, Counter
End Sub
test it
Code:
Public Sub TestRecursive()
'add 10 5 times
RecursiveAdd10 6, 50
End Sub
so the output is:
Value 50 Counter 0
Value 60 Counter 1
Value 70 Counter 2
Value 80 Counter 3
Value 90 Counter 4
Value 100 Counter 5
Final 110
Hierarchical data
When working with hierarchical data normally each record references a parentID in the same table. This is often called a "Self Referencing" table.
This would give the structure
Show table
To get this into a Tree View or display the hierarchy you start at the first Root node then recursively find the children. In the demo below I use a tree view to span the nodes to build the tree. To see how this is done look at the class module TreeViewForm.
Tree Views
Working with treeviews is pretty code heavy, but I have created a custom class to make this very easy. If you build a query precisely as instructed, you can then build a tree view with only a couple lines of code. The class also ties database information (Primary Keys) to the nodes making it much easier to work between the treeview and the data. The TreeView is "pseudo-bound" to the data. Read the instruction in the TreeViewForm class module.
I have included Multiple Demos showing different data structures and features. The E2E demo has about all the bells and whistles I added after working with DGreen on a specific solution. However to simply load your own tree view requires you to build a query and a single line of code.
1. Load from common query
2. Drag and drop of nodes and update database
3. Add, edit delete record in database and update tree
4. Delete, edit node and update database
5. Move node up and down in level and update sort order
6. Apply icons to specific records
7. dynamically load icons from folder at runtime
8 Right click on node with pop up command bar
9. Right click off node with different command bar
10. Expand and collapse tree and branch
11. Node selected to synch subform
12. Node double click to add, edit, delete
13. Auto level creates outline numbering
14. Save sort
15. Autolevel levels
16. Full load of nodes or light load and add nodes when expanded
Also included is a demo of Tree Views using MS Forms instead of the Active X.
The Span Files and Directory show a recursive span of a file system logging the information with another recursion to load the tree view.
I am realizing that the Tree View works well for navigation, but would not be our preferred method for input/queries. Too many mouse clicks. That depends on your data. I can demo some structures that adding a node and moving a node would be way faster then any other method. Treeviews work well...
Dear All i m sorry to post this topic but i spend all evening of the last 2 weeks without get any good results. i m trying to add nodes to the Tree view control on Access, but basically i have 2 cases: 1 - if i stop to the first level of nodes i don't have any issue but when i go to the...
I use the treeview control extensively. Thank you very much, Majp! However, I'm stumped at how to select the nodes I want. I use the PK to select individual records, but using it for a print selection, I'd like to be able to select the top levels and all under them would be selected. Or select a...
Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...
Good to finally get some idea of what the business process actually is. Reward seems a bit upside down - those doing most of the work get least reward. And no chance of promotion since you said movements don’t happen still confused about when someone leaves since when someone joins, the...
t is impossible that there is no solution to this problem. In any way, I want to activate this query update accounts without disrupting the user Is it reasonable that not all Access programmers have a solution? I will say it one more time, not sure what is not clear. If you are stuck on doing...
I do have a difficult project that is similar to what you are on working, that is why I approach you. OK sounds interesting. I would just post it and people will reply. It may be an area that someone has expertise. Unless it is really COI because now I know more about that than ever wanted...
www.access-programmers.co.uk
Part Two to this thread focusing on recursion and shows how to build a simulated tree view with only a listbox.
This is part two to this thread. https://www.access-programmers.co.uk/forums/threads/hierarchical-data-recursion-tree-views-and-a-custom-class-to-assist.309753/ However the original thread dealt more specifically with tree views. This does not. The above threads has many links to other treeview...
Thanks for so sharing such a great tool. I want to use tree view for one of my folder and used span folder option to populate the tblFileFolder. No of records in the table are 3000. I am then using TreeViewRecordLoader Class to generate my MS Forms tree view which add nodes from the table recursively due to which it takes about a minute or so and during which it seems that access got stuck. Is it possible to load the tree view faster instead of adding one by one node or is there any other way to speed up the tree view generation?
Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...
www.access-programmers.co.uk
The question is what is taking the time. Are you storing the file structure in a table then loading the tree or are you reading the files and loading the tree at the same time. Both of these take time.
In my example I read from the files and load the data to a table. Then I build the tree. I also do a lot of processing.
There may be more efficient ways to read and load the files to the table.
However once in a table I have the code in the class to do what I call a "light load". I only load the visible nodes. I think the demo has 10,000 nodes but I only load those needed for visibility. Maybe that is a a few hundred. Then when you click on a node it loads the next set of child nodes needed for visibility. This tree loads immediately and expands as you work with it.
If you want, I recommend starting a new thread which is easier and cleaner than adding to this long thread.