[AccessD] Drill-Down in Access

You could use the MS TreeView, on an unbound form, combined with a
just-in-time sub form.

When the form loads, open a recordset of the parent table.  Loop it one
at a time, adding a node for the parent.  Before moving to the next
record, open a recordset for child1 where parentid = current parent id,
add each child node, stopping to pick up the grandchildren for that
child.  Repeat for Child2, and her children, then move to next parent.
(Or, write the whole thing using recursion.)

As the node gets added, set the Key property (which must be a string) to
"child=" & cstr(childID) or "grandchild=" & grandchild.  Use then the
click event of the tree view to dynamically change the sub form to one
for the parent, child or grand child, bringing up the appropriate record
based on the ID.
(This is an ActiveX control, part of MScomCtLib and that presents other
issues... But I think it serves you here, though the Load may be slow.)


Hth
Steve



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mike & Doris
Manning
Sent: Tuesday, June 29, 2004 12:27 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Drill-Down in Access


I use the Janus GridEX control in .NET and absolutely love it because it
is so easy to work with.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Tuesday, June 29, 2004 11:49 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Drill-Down in Access


Is there a way to provide true drill-down in Access? By that I mean
something like shape you get when you create a simple two-table app with
a parent and a child, declare the relationship and then open the parent
table. You get the automatic (and beautiful, I might add) "outline"
presentation -- expand a parent and you can view all its children.

This works beautifully, and gives me almost exactly what I want. I can
add a third table, GrandChild, related to Child, and automatically get a
two-level drilldown.

If I add a second Child table and double-click the parent, Access asks
me which of the two children to add to the display. What I figured out
so far is this:

Select Child1, then AutoForm it. (GrandChild1 is automatically included
in the drill-down view.) AutoForm it and you get a pretty display. Save
the form as Parent1_frm.

Close the form and the table, then repeat, this time selecting Child2.
(Grandchild2 is automatically included.) Autoform it and you get a
pretty display. Save the form as Parent2_frm.

Pretty as it is, the main problem I have with this is that I'm allowing
direct table entry in the child and grandchild, and thus lose all the
cool event handlers. If I substitute a form for the Child table, I
immediately lose the drill-down effect -- which in the current app is
CRITICAL.

Q1: is there another way to get drill-down in Access, while also
retaining the events?
Q2: I note that there are (according to ads, at least) various grids
etc. available for .NET that seem to provide both drill-down and
events/validation. Maybe Access is the wrong FE for this kind of app? 

Note: what I really need to be able to do is prevent updates into a
couple of columns while permitting updates in a couple of other columns.
Binding a table gives me drill-down but no control. Binding a form, I
lose the drill-down.

Any suggestions/solutions?

TIA,
Arthur


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com