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.
The Tempquery was not deleted, and I should check that first which I was not doing.
Add the Following function
Code:
Public Function QueryExists(QueryName As String) As Boolean
Dim qdf As QueryDef
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = QueryName Then
QueryExists = True
Exit Function
End If
Next qdf
End Function
Then replace this or add the new line of code
Code:
Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
Dim qdf As QueryDef
Dim i As Integer
Dim MyParams As New Collection
Dim strMyParams As String
Dim strSql As String
For i = 0 To UBound(TheValues)
MyParams.Add "Param" & i, "Param" & i
If strMyParams = "" Then
strMyParams = "[" & MyParams(i + 1) & "]"
Else
strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
End If
Next i
strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
ParamInsert = strSql
'Debug.Print strSql
'Check to see if tempquery did not get deleted
If QueryExists("tempQuery") Then CurrentDb.QueryDefs.Delete ("tempquery")
Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
For i = 0 To UBound(TheValues)
qdf.Parameters(i) = TheValues(i)
Next i
qdf.Execute
CurrentDb.QueryDefs.Delete ("tempquery")
End Function
VBA cannot handle all file and folder names that you can use. My guess there is some special character.
You could put an error handler
Code:
Private Sub SpanFolders(SourceFolderFullName As String, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
'Dim FSO As Object 'Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder 'Scripting.Folder
Dim SubFolder As Scripting.Folder 'Scripting.Folder
Dim FileItem As Scripting.file 'Scripting.File
' Dim ParentID As Long
Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
FolderLevel = FolderLevel + 1
LogFilesFolders SourceFolder.Name, SourceFolder.Path, SourceFolder.Type, ParentID, fft_Folder, FolderLevel
ParentID = GetFolderID(SourceFolder.Path)
For Each FileItem In SourceFolder.Files
'2 and 4 are system and hidden files
If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 Then
LogFilesFolders FileItem.Name, FileItem.Path, FileItem.Type, ParentID, fft_File, FolderLevel
End If
Next FileItem
For Each SubFolder In SourceFolder.SubFolders
ParentID = GetFolderID(SourceFolder.Path) ' The record has just been added so get PK by name
' LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
SpanFolders SubFolder.Path, ParentID, FolderLevel
End If
Next SubFolder
Set FileItem = Nothing
Set SourceFolder = Nothing
End Sub
My guess is the sourcefolderfullname . In the error handler print that out.
That is not the code I have?
You already have an error handler section, so I added the SourceFolderFullName to the MSGBOX output.
This was the first offending file
Then I get almost immediately after trying to run it again.
and this in the debug window.
I will add the file name to the summation error message as well.
1054_FolderFiles current
1055_FolderFiles current
1056_FolderFiles current
1057_FolderFiles current
1058_FolderFiles current
1059_FolderFiles current
1060_FolderFiles current
1061_FolderFiles current
1062_FolderFiles current
1063_FolderFiles current
1064_FolderFiles current
1065_FolderFiles current
1066_FolderFiles current
1067_FolderFiles current
1068_FolderFiles current
1069_FolderFiles current
1070_FolderFiles current
1071_FolderFiles current
1072_FolderFiles current
1073_FolderFiles current
1074_FolderFiles current
1075_FolderFiles current
Edit: That is not the error being displayed from SpanFolderSummations, as I thought it might be?
The biggest feature I would still like to add is a "Light Load". Currently it loads all of the nodes on open. In this example no problem. I have a tree with 50K nodes and that can take a real long time. Ideally you would simply add the top level, and then when the user expands a node it only loads the next level for that node. Unfortunately there is a problem doing that. If you do not add the child node (at least 1) there is no plus symbol so you do not know to expand it. The trick I am thinking is adding the first child node per each node in the level. That one is going to take some thinking but I think it is doable. That is not specific to your case
For your's need to try to update the autolevel values on drag and drop without having to reload the whole tree.
Besides that I think you have covered about as many features as you can cram into a tree view.
If you just show the plus sign anyway, and a message saying "nothing to display" when clicked, would that be more irritating?
Alternatively could you trick the plus into only appearing where there is an expansion. You would only need to read a single node to check, if the required shallow depth had been reached. You could use "node exists" plus "shallow depth " reached as a trigger to end the recursion and just show the plus sign. Something like that?
@gemma-the-husky
Unfortunately, you did not see the threads below that thread. That problem was solved and incorporated into the class as a major feature. Immediately after that thread I followed with the solution and provided the code and the updated class for doing a light load. The class allows you to do a full or light load. This works great. It does this by adding a single child node under the visible nodes. Then when you expand it flushes out the current level and again adds a single child node to the visible node. In the demo there is a light load example with 10k nodes and loads instantaneously.
If you just show the plus sign anyway, and a message saying "nothing to display" when clicked, would that be more irritating?
That is not doable since there is no property to enforce the expansion sign. The expansion sign only exists if there is a child node. It is a function of the ActiveX control. You can do the opposite and there is a property not to show any expansion signs. I do not know the purpose of that. You have no idea what ones are expandable.
Alternatively could you trick the plus into only appearing where there is an expansion. You would only need to read a single node to check, if the required shallow depth had been reached. You could use "node exists" plus "shallow depth " reached as a trigger to end the recursion and just show the plus sign. Something like that
That is basically exactly what the code does. It only puts a single child node per visible node and only flushes it out when you expand. See post 112 and 113.
I did this for the ActiveX control and currently working to do it for the JKP control that uses MS Forms.
, I should also be able to get the code looking like the VB editor,
dba.stackexchange.com
