Vicious Circle

[Guest]

I have a form with two sub-forms, one of which itself has a sub-form (a sub-sub-form). They all work fine and remain nicely synchronized. However, the sub-sub-form has a combo box based on a table which is updated according to variables calculated in the VBA associated with the main form and in the other sub-form (not the parent of the sub-sub-form) and herein lies the problem. The sub-sub loads before the code runs in the unrelated sub-form and, of course, in the main form. Consequently, the sub-sub-form opens without the required options set in its combo box.
I cannot currently see an easy way around this other than, perhaps, for the main form to have just the sub-form which is not the parent of the sub-sub-form and then to open a dummy form from the main form, making the parent of the sub-sub-form a sub form of the dummy form. That would get the sequencing right but I cannot but help thinking that there should be an easier way, particularly because, apart from the one combo box, the original design works so well
I thought that a well-placed requery of the sub-sub-form might work but I am not quite sure how. Incidentally, how does one refer to the sub-sub-form from the main form? I would be tempted to write Forms![frm_Parent_Form]![sfrm_Child_SubForm]![sfrm_Grandchild_Sub-subForm] but I have come unstuck with that sort of reasoning in the past.

 

[Graham R Seach]

Peter,

Subforms are controls on the parent form, so you can refer to a subform by
it's Name property on the parent form. For example, if you have a main form
containing two subforms, named "subform1" and "subform2" respectively, then
(from the main form) you can refer to them thus:

Me.subform1.Form.Caption = "I changed the caption"
Me.subform2.Form.Caption = "I changed the caption to this"

(Open the main form in design view, click the subform, and check its Name
property. That's the name you use).

So, if subform1 itself has a subform called "subsubform", you can refer to
it thus (from the main form):

Me.subform1.Form.subsubform.Form.Caption = "This caption changed too"

To refer to one subform from another, use this syntax:
Me.Parent.Form.subform2.Form.Caption = "Caption changed from subform1"

To refer to one subsubform from another subsubform, do this:
Me.Parent.Form.Parent.Form.subform2.Form.subsubform.Form.Caption =
"Caption changed from subform1.subform"

Armed with all this knowledge, you can now apply sequenced requeries.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I have a form with two sub-forms, one of which itself has a sub-form (a

sub-sub-form). They all work fine and remain nicely synchronized. However,
the sub-sub-form has a combo box based on a table which is updated according
to variables calculated in the VBA associated with the main form and in the
other sub-form (not the parent of the sub-sub-form) and herein lies the
problem. The sub-sub loads before the code runs in the unrelated sub-form
and, of course, in the main form. Consequently, the sub-sub-form opens
without the required options set in its combo box.

I cannot currently see an easy way around this other than, perhaps, for

the main form to have just the sub-form which is not the parent of the
sub-sub-form and then to open a dummy form from the main form, making the
parent of the sub-sub-form a sub form of the dummy form. That would get
the sequencing right but I cannot but help thinking that there should be an
easier way, particularly because, apart from the one combo box, the original
design works so well.

I thought that a well-placed requery of the sub-sub-form might work but I

am not quite sure how. Incidentally, how does one refer to the sub-sub-form
from the main form? I would be tempted to write
Forms![frm_Parent_Form]![sfrm_Child_SubForm]![sfrm_Grandchild_Sub-subForm]
but I have come unstuck with that sort of reasoning in the past.

 

[Guest]

Thanks for that, Graham. You MVPs have been worth your weight in gold over the past couple of weeks. I am very grateful for your help.

You have now provided an answer for the second part of my question but I am still firmly stuck with the first bit. I am just hoping that someone can come up with an answer before I have to take my nice form to bits and substitutue something much less elegant (and satisfying!) I occasionally kid myself that I am getting somewhere with Access but the feeling never lasts long.

In respect of the first part of my question, I find it difficult to understand why sub-forms load and run before their parents. This is, of course, the source of my current difficulty. Since a sub-form is related to its parent it seems only natural that it will often need to inherit more than just its parent's editing constraints.

With Australsia now asleep, perhaps someone around the other side of the globe might like to wake up to someone else's headache(?)

 

[Guest]

I was clearly too quick off the mark before I wrote my reply. Having reread your explanation, it became clear that the answer to the first part of my question was contained within it after all, so I applied it to my situation and everything is now operating perfectly. Thank you very much. Your excellent advice has enabled me to complete the vicious circle by re-entering the back door. I am feeling a bit like the Oozellum bird

Grateful regard