MS Access nested subforms link to main (parent) form

Question

I am busy developing an MS Access 2007 application that requires the use of subforms with quite a complex user interface. I am aware on how to create a parent form and link to a subform so that it shows data relating to the parent form record, but my question is this:

If I have multiple nested subforms (say 3 or 4 levels down), can I somehow link a subform sitting on the 4th level with the record of the parent form on the 1st level? What I mean by nested subforms is having a form pasted inside a form, pasted inside another form, etc.

The subform that I wish to link with the parent form is currently using a query as its source object and the reason for using nested subform is purely for the aesthetics of the user interface.

Is there a simple way to go about this? Or perhaps using VBA?

Answer 1

One thing that many people don't realize is that the LinkChild/LinkMaster fields can actually be any valid expression. So, you could have subForm1's LinkChild/LinkMaster properties be:

  LinkMaster  CustomerID
  LinkChild   CustomerID

Then subForm2 could have this:

  LinkMaster  subForm1.Form!InvoiceID
  LinkChild   InvoiceID

...and so forth. I can't imagine going much deeper than that, but it's possible.

You might also consider if a cascading datasheet form might do the trick, using subdatasheets. You can even have a datasheet display a non-datasheet form as its subdatasheet (something I disconvered entirely accidentally when I switched a a parent form having a subform into datasheet view -- the subform remained displayed in form view). With subdatasheets, you don't have any issues with number of forms, or the limitation on embedding continuous forms.