Television episode tracker

[KitaYama]

Would be nice to provide link to MajP's sample.

Here's @MajP's class. How to make it and the sample file

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

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...

www.access-programmers.co.uk

TreeView and ListVIew are ActiveX controls and come with their own set of issues and learning curve.

I'm not sure about their issues, but the only learning curve is how to make the necessary query. Me, as a newbie, was able to learn how it should be done in no time. Once the query was ready, all the heavy work was done in the class. Nothing to worry about.

 

[June7]

My exercise did not involve custom class module. I built forms and controls from scratch. All code behind forms.

Just downloaded MajP demo. Right off I get same error as in my db when opening form. Library reference is okay so that is not cause. I OK through error and form sorta works but error keeps popping up. I suspect it has something to do with upgrade to Windows11 because last time I looked at this was before upgrade.

Don't know how you got away with just making a query to incorporate into your db.
Understanding how to set up data structure was first hurdle for me.

 

[ilikecats]

I take look at the database Edgar made. Thanks everyone.

 

[KitaYama]

My exercise did not involve custom class module. I built forms and controls from scratch. All code behind forms.

Just downloaded MajP demo. Right off I get same error as in my db when opening form. Library reference is okay so that is not cause. I OK through error and form sorta works but error keeps popping up. I suspect it has something to do with upgrade to Windows11 because last time I looked at this was before upgrade.

Don't know how you got away with just making a query to incorporate into your db.
Understanding how to set up data structure was first hurdle for me.

@June7 As I said I'm not in front of my PC and I can not check, but I don't (didn't?) have problems with his tree view class in Windows 11.
Using a remote desktop, I have access to my files.

Three databases are attached.
Majp_TreeviewDemo_v3.accdb is a very old version of his class. It lacks a lot of functionalities. It's OK on Win11.

MajP_TreeviewDemo_v18.accdb is a test database I made a while back based on his tree view version 18. It runs without error on win11. It's a tree view to show the details of the ribbon of one of my databases. Open it, expand the tree. You can drag and drop or right click on nodes and having more options.

TV_Series.accdb shows a tree view of series. I deleted everything and left only the tree view to be used as a subform.

As I said, the key is making the queries. One query for each level (node) of the tree and then a union query to join them.

This is a much more advanced version of MajP's tree view for managing the assets.
This one has no error in Win11 too.

Access Item Genie demo for Organizing / Tracking your Items

I started working on another form idea to display a larger image after you click on the smaller image in the item list. Just using a popup form. I just started to realize the pictures are just to small to look at when it's needed to see more detail. That's a fairly easy modification. Just...

www.access-programmers.co.uk

Hope it helps.

 

[June7]

I get same error with your v18 file as well: "The expression MouseDown you entered as the event property setting produced the following error: There was an error loading an ActiveX control on one of your forms or reports." I OK past that then click on the TreeView +AppRIbbon item and get error: "The expression Expand you entered as the event property setting produced the following error: There was an error loading an ActiveX control on one of your forms or reports." Couple more clicks on TreeView triggers popups with variations of same message (KeyUp, Collapse, Click, etc) and then they stop. Additional clicks to expand/collapse work fine. Close and reopen form and starts all over again with popups. This is actually performing worse than MajP's and mine.

Search indicates this is a common issue.

Good thing I never needed such feature in db I developed.

 

[KitaYama]

I get same error with your v18 file as well: "The expression MouseDown you entered as the event property setting produced the following error: There was an error loading an ActiveX control on one of your forms or reports." I OK past that then click on the TreeView +AppRIbbon item and get error: "The expression Expand you entered as the event property setting produced the following error: There was an error loading an ActiveX control on one of your forms or reports." Couple more clicks on TreeView triggers popups with variations of same message (KeyUp, Collapse, Click, etc) and then they stop. Additional clicks to expand/collapse work fine. Close and reopen form and starts all over again with popups. This is actually performing worse than MajP's and mine.

Search indicates this is a common issue.

Good thing I never needed such feature in db I developed.

It's because of what has been explained in this post.
You may want to follow Sonic8's suggestion on #2 of the same thread. It may solve the problem.


Do you receive the same error on tvSeries.accdb?

Good thing I never needed such feature in db I developed.

The errors you see is not a part of tree view. It's my fault because my system locale is not the same as yours. It's a character set problem because of my language is set to UTF.

 

[June7]

Appear to have fixed this issue in my db. TreeView ImageList property was not set to the ImageList1 control. Even though I set this property and build image list in VBA, seems to need this property set in design. Don't know if this was ever set because I remember this control worked fine when first built.

Doesn't explain why MajP and your db won't work for me.

Now maybe I will tackle ListView and figure out why it no longer works.

 

[MajP]

Here is a treeview demo. I think the original solution is better becuase more options in formatting. If you were adding, editing, deleting info then maybe use a treeview, but I cannot imagine you would do that. The information is fixed and known.
For the amount of levels and nodes the tree view does not give much benefits. Where you really get the benefit is when you nested levels get more than three or if some sub levels are very long.

 

[KitaYama]

Here is a treeview demo. I think the original solution is better becuase more options in formatting. If you were adding, editing, deleting info then maybe use a treeview, but I cannot imagine you would do that. The information is fixed and known.
For the amount of levels and nodes the tree view does not give much benefits. Where you really get the benefit is when you nested levels get more than three or if some sub levels are very long.

View attachment 120505

I used your class and added a treeview in #25. I was hopping to see a tree view to be used instead of the report, as a subform.
I was able to start the nodes from seasons, but I wasn't able to filter the tree, when a record in parent form is selected.

Is it possible to use your class for a sub form, and being linked to the parent form?

thanks

 

[MajP]

Is it possible to use your class for a sub form, and being linked to the parent form?

Yes. You simply have to filter the query you are using and reload the tree.

 

[KitaYama]

Yes. You simply have to filter the query you are using and reload the tree.

Sorry for asking this dumb question. But how can I filter the query?
The initialize process of the class, needs a query name, which is a union query that show all record. If the initialize sub needed a SQL, I could dynamically create a filtred SQL and pass it.

I was thinking of using querydef to re-write the union query's SQL and add a filter, but I'm not sure if it's what you mean.

Thanks again.

 

[MajP]

I was thinking of using querydef to re-write the union query's SQL and add a filter, but I'm not sure if it's what you mean.

Yep that is how I do it.

 

[KitaYama]

@MajP Million thanks
It seems that I was on the correct path (re-assigning the sql of the query).

Thanks again.

 

[MajP]

@MajP Million thanks
It seems that I was on the correct path (re-assigning the sql of the query)

For this problem, If you do not need all the additional functionality you get with the class then loading the tree might be easier to roll your own. Because building the right queries takes time and accuracy in the naming. And probably a good practice

Writing the code may be actually easier
1. Get the distinct seasons for the series
2. Loop the seasons and add as a root node
4. Create a nested loop and get the episodes for that season and add the nodes as child for that season


another way is to add all the nodes as root nodes and then move them under there parents. Code may be a little shorter. Instead of nested loops you have two simultaneous loops

Populating a TreeView control from a recordset without Recursion?

I was playing with the tree view control and found I can just populate all nodes of a tree view control and then assign a parent to each of them with another pass... You guys see any problem in that method? I know there's a bunch of threads about this topic by @MajP, but this is likely a...

www.access-programmers.co.uk

get the distinct seasons and just add to the tree as root nodes
then add all the episodes as root nodes.
now loop all the nodes and reassign the nodes to the correct parent. Setting the parent property moves the node to the correct parent.

 

[KitaYama]

For this problem, If you do not need all the additional functionality you get with the class then loading the tree might be easier to roll your own. Because building the right queries takes time and accuracy in the naming. And probably a good practice

Writing the code may be actually easier
1. Get the distinct seasons for the series
2. Loop the seasons and add as a root node
4. Create a nested loop and get the episodes for that season and add the nodes as child for that season


another way is to add all the nodes as root nodes and then move them under there parents. Code may be a little shorter. Instead of nested loops you have two simultaneous loops

Populating a TreeView control from a recordset without Recursion?

I was playing with the tree view control and found I can just populate all nodes of a tree view control and then assign a parent to each of them with another pass... You guys see any problem in that method? I know there's a bunch of threads about this topic by @MajP, but this is likely a...

www.access-programmers.co.uk

get the distinct seasons and just add to the tree as root nodes
then add all the episodes as root nodes.
now loop all the nodes and reassign the nodes to the correct parent. Setting the parent property moves the node to the correct parent.

Seems something new to work on and learn during this week end.
thanks again.

 

[Edgar_]

I was hopping to see a tree view to be used instead of the report, as a subform

Sorry about that, I merely tried to replicate OP's screenshot the best I could.

In a real world scenario, it would probably be better to utilize one of the REST APIs out there and a web browser handles that much better than Access' built-in tools.

 

[KitaYama]

Sorry about that, I merely tried to replicate OP's screenshot the best I could.

Yes, I was aware of that. I just wonder why you used a tree view for the series titles. It could be a continuous form as well.

In a real world scenario, it would probably be better to utilize one of the REST APIs

Not sure, but I assume OP needs to manage the series s/he has watched. I have a feeling that using an API, will bring in a lot of un-necessary data, or at least find a way to filter it.

Thanks

 

[tvanstiphout]

Re API: I made it work with Postman (which is a tool to call REST APIs). I'm sure it would work with the VBA-Web library.
The OP would be well advised to exercise this API so you can see what data is available, which may influence your db design.

There are several APIs available related to series:

A fragment of the result of the call to list all series follows.
{
"status": "success",
"data": [
{
"id": 70327,
"name": "Buffy the Vampire Slayer",
"slug": "buffy-the-vampire-slayer",
"image": "/banners/posters/70327-1.jpg",
"nameTranslations": [
"ces",
"dan",
"deu",
"eng",
"fin",
"fra",
"heb",
"hun",
"ita",
"por",
"rus",
"spa",
"tur",
"pt",
"ara",
"nld",
"ukr"
],
"overviewTranslations": [
"ces",
"deu",
"eng",
"fin",
"fra",
"heb",
"hun",
"ita",
"por",
"rus",
"spa",
"swe",
"tur",
"pt",
"ara",
"nld",
"ukr"
],
"aliases": [
{
"language": "deu",
"name": "Buffy - Im Bann der Dämonen"
},
{
"language": "spa",
"name": "Buffy, cazavampiros"
}
],
"firstAired": "1997-03-10",
"lastAired": "2003-05-20",
"nextAired": "",
"score": 504765,
"status": {
"id": null,
"name": null,
"recordType": "",
"keepUpdated": false
},
"originalCountry": "usa",
"originalLanguage": "eng",
"defaultSeasonType": 1,
"isOrderRandomized": false,
"lastUpdated": "2025-07-06 15:13:21",
"averageRuntime": 44,
"episodes": null,
"overview": "In every generation there is a Chosen One. She alone will stand against the vampires, the demons and the forces of darkness. She is the Slayer.\r\n\r\nBuffy Summers is The Chosen One, the one girl in all the world with the strength and skill to fight the vampires. With the help of her close friends, Willow, Xander, and her Watcher Giles she balances slaying, family, friendships, and relationships.",
"year": "1997"
},
And just for fun: this is in the /artworks API:

 

[Edgar_]

I have a feeling that using an API, will bring in a lot of un-necessary data, or at least find a way to filter it.

The ones I've seen come with an endpoint to fetch "now playing" or "popular", they come with pagination parameters, so you can easily browse through them. They also come with endpoints to search by title, year, genre, etc. You don't really need a db for that, because a browser control can simply print the data as it comes, just like a regular browser. However, to work with an API within Access, particularly for using continuous forms, you do require temp tables, unless you employ some hacks.

 

[CJ_London]

Two potentially useful flags are ‘watched’ (which might be a date) and ‘wish list’