UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting A Treeview Control To Work, Access 2016    
 
   
airplayne
post Jun 11 2018, 08:52 PM
Post#1



Posts: 544
Joined: 17-February 09
From: West Texas


I have been, metaphorically, banging my head against a wall on this and really need some guidance. I am trying to get a treeview control to work and simply have not found a way to do it. I have watched some tutorials and have tried a couple different solutions. One is the activex control (I know this is taboo, but whatever) and one that was developed a few years ago as a solution for not wanting to deal with the control if it stops working due to an update.

The other solution I found was from another thread in this forum on the same topic.

What I have attempted to do is use the Northwind database to try simply building a treeview structure based on the orders table. For some reason I am running into issues getting the SELECT statement to work. Somehow I get the feeling it has something to do with the fact that whomever designed that database like to put spaces in their table names, query names, form names, etc...

Can anyone get me sent in the right direction on this?
Go to the top of the page
 
TheSmileyCoder
post Jun 12 2018, 01:14 AM
Post#2


UtterAccess VIP
Posts: 1,523
Joined: 19-January 12
From: Denmark, Copenhagen


Hi
If you have a specific issue we can assist with that. But with a question this generic, the best we can do is just provide links to tutorials. Are my treeview tutorial videos among the ones you have seen? They are all for the ActiveX control, link is in my signature.

Anders

--------------------
TheSmileyCoder // Anders Ebro (Access MVP)

~~~~~~~~
Blog: www.TheSmileyCoder.com
YouTube Channel: TheSmileyCoder
Go to the top of the page
 
Phil_cattivocara...
post Jun 12 2018, 01:42 AM
Post#3



Posts: 199
Joined: 2-April 18



Until native ( = Microsoft) ActiveX Treeview control is available, use it.
If you have problem with 64bit Access version there are some commercial products but... it's your choice.
If you are above the average Access developer you can try with this [MsAccess] TreeView VBA (it's always a work-in-progress demo, italian)
For completness I am not that kind of "above the everage Access developer".

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
airplayne
post Jun 12 2018, 07:05 AM
Post#4



Posts: 544
Joined: 17-February 09
From: West Texas


@thesmileycoder, I have looked at your first tutorial on the activex control, including downloaded the demo database. I was able to see that work, but when I tried to redo what you had done inside another database I ran into problems getting the query to work. It just would not allow the code to compile.

To get moving in the right direction, if I was to use the Orders table in the Northwind database how would I go about grabbing all the information in that table and applying the treeview? The initial approach I tried was to first sort by Order Date then by Customer. Is there some special way to handle objects (field names) that have spaces in them?
Go to the top of the page
 
GroverParkGeorge
post Jun 12 2018, 07:18 AM
Post#5


UA Admin
Posts: 33,477
Joined: 20-June 02
From: Newcastle, WA


"I ran into problems getting the query to work. It just would not allow the code to compile. "

Sometimes, a specific description helps. "problems getting the query to work"? What, precisely, did you try? What, precisely, was the problem, an error? The wrong results? No results?

Where in the VBA did Access fail to compile? Which lines?

We have no visibility to your environment, nor your computer, nor to the database on which you are working. We only know what you share here.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
airplayne
post Jun 12 2018, 08:18 AM
Post#6



Posts: 544
Joined: 17-February 09
From: West Texas


Here is what I have tried just to put together a simple tree view in the Northwind database:
CODE
Public Sub loadTreeView()
    Dim tv As MSComctlLib.TreeView
    Set tv = Forms("frmTreeView").tvCtrl.Object
    
    tv.Nodes.Clear
    
    Dim rsOrders As DAO.Recordset
    Set rsOrders = CurrentDb.OpenRecordset("SELECT * FROM Orders ORDER BY Order Date,Customer", dbOpenDynaset)
    
    Dim strFind As String
    strFind = "Order ID=30"
    rsOrders.FindFirst strFind
    Dim nodX As MSComctlLib.Node
    
    Do While Not rsOrders.NoMatch
        Set nodX = tv.Nodes.Add(, , , rsOrders!Customer)
        nodX.Bold = True
        rsOrders.FindNext strFind
    Loop
End Sub


The error I am getting is in the line where I am setting the value of the rsOrders variable. It is saying there is a problem with the ORDER BY clause.

Go to the top of the page
 
GroverParkGeorge
post Jun 12 2018, 08:34 AM
Post#7


UA Admin
Posts: 33,477
Joined: 20-June 02
From: Newcastle, WA


What is the name of the field in the table: "OrderDate" or "Order Date"?

If the latter, this is s avery good illustration of the problem with using names containing spaces. You'll have to delimit such names: [Order Date].

the better solution would be to avoid special characters and spaces in names entirely.



--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
airplayne
post Jun 12 2018, 08:56 AM
Post#8



Posts: 544
Joined: 17-February 09
From: West Texas


Ok, it looks like this is working:

CODE
    Set rsOrders = CurrentDb.OpenRecordset("SELECT [Order Date] FROM Orders ORDER BY [Order Date]", dbOpenDynaset)


The problem I am running into now is organizing the information in the tree view by date. The way the code is structured does not lend itself to that kind of approach. I am wanting to put a month at the top of the tree view, then expand out by individual dates, then show individual orders for each date. Is this accomplished simply by setting the first node somehow, or is there a root I can start with?
Go to the top of the page
 
TheSmileyCoder
post Jun 13 2018, 01:46 AM
Post#9


UtterAccess VIP
Posts: 1,523
Joined: 19-January 12
From: Denmark, Copenhagen


No you would have to add those nodes first. One approach could be to add the month nodes "on demand". I.e. while adding an order from january, check whether the treeview already contains a january node, and if not, add it, then add the order, with its parent node being the month node.

You could also start out by adding all the month nodes. This is the easier approach, but might not be as visually appealing since you might get empty month nodes.

--------------------
TheSmileyCoder // Anders Ebro (Access MVP)

~~~~~~~~
Blog: www.TheSmileyCoder.com
YouTube Channel: TheSmileyCoder
Go to the top of the page
 
airplayne
post Jun 13 2018, 02:23 PM
Post#10



Posts: 544
Joined: 17-February 09
From: West Texas


Here is what I have attempted so far, but it is not working:
CODE
Public Sub loadTreeView()
    Dim tv As TreeView
    Set tv = Forms("frmTreeView").tvCtrl.Object
    
    tv.Nodes.Clear
    
    Dim rsOrders As DAO.Recordset
    Set rsOrders = CurrentDb.OpenRecordset("SELECT * FROM qryOrders", dbOpenDynaset)
    
    Dim nodX As Node
    
    Dim strBook As String
    Do While Not rsOrders.EOF
        Set nodX = tv.Nodes.Add(, , , rsOrders!Month)
        nodX.Bold = True
        strBook = rsOrders.Bookmark
        addChildren tv, nodX, rsOrders, rsOrders!OrderDate
        rsOrders.Bookmark = strBook
        rsOrders.MoveNext
    Loop
        
    
End Sub

Private Sub addChildren(tv As TreeView, nodParent As Node, rsOrders As DAO.Recordset, strParent As String)
    Dim nodX As Node
    Dim strBook As String
    Do While Not rsOrders.EOF
        Set nodX = tv.Nodes.Add(nodParent, tvwChild, , rsOrders!Month)
        strBook = rsOrders.Bookmark
        addChildren tv, nodX, rsOrders, rsOrders!Month
        rsOrders.Bookmark = strBook
        rsOrders.MoveNext
    Loop
        
End Sub


Before I tried getting to the child nodes I was able to see a list of dates in the treeview when I simply pointed the nodX variable at the Order Date field, which I renamed OrderDate in the query to make things easier. I then was also able to see a list of months when I pointed nodX to the Month field I setup in the query to format the date.

Basically, when I tried to run this I get an error saying there is no current record.

***Quick update on this, I am actually now getting an error stating that I am out of stack space. The value: January/06 does show up in the treeview, but I am getting this error. Any thoughts?
This post has been edited by airplayne: Jun 13 2018, 02:38 PM
Go to the top of the page
 
airplayne
post Jun 13 2018, 03:17 PM
Post#11



Posts: 544
Joined: 17-February 09
From: West Texas


Ok, I changed the dbOpenDynaset to dbOpenForwardOnly. That seems to have solved the stacking issue, but now I am getting an error on the strBook = rsOrders.Bookmark line. It is giving my an Invalid Operation. Is there some sort of circular reference thing I am not aware of?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2018 - 05:38 AM