Expert: Scottgem - 7/15/2011

Question
QUESTION: I have several tables that are chained to each other 1-to-Many in a straight line, like Clients --> Orders --> Products --> Components --> Parts --> Materials --> Formulas --BoQ. I would like to make a single aggregate form to give the user ease of access in using the app.

I have used MS Access off-and-on since 1995, and I am fairly good at programming in VB6, .NET and SQL, so I know about subqueries, etc, and I also know how fast nested subqueries can become unwieldy. Of course, I will have separate forms for managing individual tables and will provide the user with handy links to these from the main form.

1. The $6M question is: is it possible or even advisable to attempt such a feat in Access?

2. While subforms filter records based on the selected parent record, some of my subforms will filter records on the grandparent record, e.g. show ALL Products in the order, all Components of each product, and cascading downwards to BoQ.

I shall be grateful for any advice on this.

2. As opposed to subforms that filter the records based on the selected parent record, some of these subforms will need to filter records on the grandparent record, e.g. show ALL Products in the order, Components of each product, and cascading downwards to BoQ.

I shall be grateful for any advice on this.

ANSWER: The first problem you will run up against is that you can't nest a continuous form or datasheet subform within another continuous form or datasheet subform. The way I get around this is use listboxes instead of subforms. But that will get unwieldy as you get down the line.

The solution I might suggest is using a drill down technique. Use synchronized forms instead of embedded subforms. Let the user decide how far they need to drill down.

Another option is to put all the data into one query (I assume this form is not for editing but viewing) and use a tabbed interface to show the lists.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks for the reply. Very concise and helpful.
Re your question, the form is going to be editable, not just for viewing.
As for the nesting, I have figured out that the nesting will stop at the Components subform. Orders is the main form and Client will be picked from a combobox. When the user selects each product for the order, he/she will be prompted for the quantity/count and dimensions of each.
The rest, [Components --> Parts --> Materials --> Formulas --BoQ] should be computed automatically based on the formulas and grouped logically under their parent records.
Will give you a shout if I get stuck.
Thanks for all your help.

Answer
OK, if the form is being used for editing, then using a multi-table query will probably not work.

Otherwise see what you can work out.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA