By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

Idea for a fake treeview

P: n/a
I've a requirement for a treeview control to display a master-detail view.
So instead of the old the old 'ActiveX + 1,000 lines of code' method
that I've used in the past. I decided to try a different approach.

This is a very simple idea so I'd be surprised if it hasn't been done
before - but I couldn't find anything in google, so here it is if anyone is
interested.

The idea is to use a union query, where the first part of the union returns
the master records and the second part returns the selected detail. For
example, in the Northwind database, to show all orders with the expanded
details for a single selected order, you would conceptually do:

<select orders>
union
<select order detail for a single selected order>

A little formatting, and get your sorting right and you're in business. The
actual sql looks like this:

----------------------------------------------------------------------------------------------
select IIf(o.orderID=Forms![frmOrders].[txtOrderID],'-','+') AS rowID,
o.OrderID & ': ' & c.CompanyName as company,
format$(o.OrderDate, "medium date") as orderDate, 1 as detailLevel,
o.OrderID as sortOrder
from Orders as o inner join Customers as c on o.CustomerID=c.CustomerID

union all

select '', ' - ' & p.productName, null, 2, d.OrderID
from orderDetails as d
inner join products as p on d.ProductID = p.productID
where d.OrderID = Forms![frmOrders].[txtOrderID]

order by sortOrder, detailLevel;
----------------------------------------------------------------------------------------------
There's a trick to getting the '+' and '-' indicators and it's in the first
line of the query.

Place an invisible text box and a visible list box on your form. Set the
list's rowsource to the query above. Use code in
the lists OnDoubleClick event to place the selected OrderID in the text box,
(expand the detail), or replace it with zero if the orderID's match,
(close the detail).

Here's a screen shot:

http://www.assaynet.com/downloads/access/treeview.jpg

Anyway, this is a very simple example. There's a sample database at
http://www.assaynet.com/downloads/access/treeview.mdb if anyone is
interested, (Access 2002 format).

Of course there are lots of limitations. No images for one thing. No
multi-select. You can't expand more than one order, (that one should be easy
to solve though). Anyway, I'd like to know if anyone else has other ideas or
comments on duplicating treeview controls in Access. Or if anyone is
interested and wants to see some features let me know and I'll see if I
can get them put in.


Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
John,

What do the plus and minuses do?

Thanks,

Steve
"John Winterbottom" <as******@hotmail.com> wrote in message
news:38*************@individual.net...
I've a requirement for a treeview control to display a master-detail view.
So instead of the old the old 'ActiveX + 1,000 lines of code' method
that I've used in the past. I decided to try a different approach.

This is a very simple idea so I'd be surprised if it hasn't been done
before - but I couldn't find anything in google, so here it is if anyone is interested.

The idea is to use a union query, where the first part of the union returns the master records and the second part returns the selected detail. For
example, in the Northwind database, to show all orders with the expanded
details for a single selected order, you would conceptually do:

<select orders>
union
<select order detail for a single selected order>

A little formatting, and get your sorting right and you're in business. The actual sql looks like this:

-------------------------------------------------------------------------- -------------------- select IIf(o.orderID=Forms![frmOrders].[txtOrderID],'-','+') AS rowID,
o.OrderID & ': ' & c.CompanyName as company,
format$(o.OrderDate, "medium date") as orderDate, 1 as detailLevel,
o.OrderID as sortOrder
from Orders as o inner join Customers as c on o.CustomerID=c.CustomerID

union all

select '', ' - ' & p.productName, null, 2, d.OrderID
from orderDetails as d
inner join products as p on d.ProductID = p.productID
where d.OrderID = Forms![frmOrders].[txtOrderID]

order by sortOrder, detailLevel;
-------------------------------------------------------------------------- -------------------- There's a trick to getting the '+' and '-' indicators and it's in the first line of the query.

Place an invisible text box and a visible list box on your form. Set the
list's rowsource to the query above. Use code in
the lists OnDoubleClick event to place the selected OrderID in the text box, (expand the detail), or replace it with zero if the orderID's match,
(close the detail).

Here's a screen shot:

http://www.assaynet.com/downloads/access/treeview.jpg

Anyway, this is a very simple example. There's a sample database at
http://www.assaynet.com/downloads/access/treeview.mdb if anyone is
interested, (Access 2002 format).

Of course there are lots of limitations. No images for one thing. No
multi-select. You can't expand more than one order, (that one should be easy to solve though). Anyway, I'd like to know if anyone else has other ideas or comments on duplicating treeview controls in Access. Or if anyone is
interested and wants to see some features let me know and I'll see if I
can get them put in.


Nov 13 '05 #2

P: n/a

"Steve" <no****@nospam.spam> wrote in message
news:kV******************@newsread3.news.atl.earth link.net...
John,

What do the plus and minuses do?

Thanks,

Steve


They show you whether the order is expanded, (to show the order detail), or
not.
Nov 13 '05 #3

P: n/a
John,

Interesting idea. I tried something similar using subdatasheets, which
are basically nested subforms in datasheet view. You automatically get
your + and - boxes, and you can do multiple expansions.

It worked fine, except for one drawback: you couldn't get the
Form_Current event to fire reliably on a subdatasheet form (at least in
A2000). It simply wouldn't fire if there was just one record in the
child.

So, I went back to the ActiveX TreeView control. Its good to know that
there are simple, no-code solutions for situations where you don't need
the full capabilities of TreeView.

-Ken

Nov 13 '05 #4

P: n/a
John Winterbottom wrote:
to solve though). Anyway, I'd like to know if anyone else has other ideas or comments on duplicating treeview controls in Access. Or if anyone is


I like it. I did a treeview using VB6 with Access as the backend a few
years ago for a company that does corporate events that allowed them to
pick which event to edit or view. They would have been just as happy
with your idea.

James A. Fortune

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.