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.
