Set Form Properties on SubSubform from the Main Fo

Question

In Access 2007 (or 2010), I need to set the properties on a subform, embedded within the first subform, from the main form using VBA. To make it clear which form I'm talking about, I'll call the this form "subSubform" as I do in my code (well, subSubform is actually the name of the sub-subform control).

When the form loads I have a subform control on my first subform that's blank (does not contain a form source object). If needed, I fill out the SourceObject property of this control with the name of a valid form, as well as the LinkMasterFields and LinkChildFields properties. This all appears to work fine because I do get an expandable plus sign that otherwise wouldn't be there.

Next I try to set the RecordSource for the subSubform and I get an error:

2455 You entered an expression that has an invalid reference to the property Form/Report.

Here's the code I'm using:

Me!subform1.Form!subSubform.Form.RecordSource = sSubformSQL
'and I've tried this with the same bad results
Me.subform1.Form.subSubform.Form.RecordSource = sSubformSQL
'and this too
Forms("frmQuery").subform1.Form.subSubform!Form.RecordSource = sSubformSQL

'And I've tried this. It fails too, on the last line with the same error:
    Dim frm As Form
    Set frm = Forms("frmQuery").subform1.Form
    Dim frm2 As Form
    Set frm2 = frm.subSubform.Form

I've tried setting a timer and waiting one second to run the above code, just in case it takes some time for the form to load. But this makes no difference.

I've tried running code from the first subform instead of from the main form, but that doesn't help either. Still fails with the same error.

FWIW, both my subform and my subSubform are DataSheet views. I don't think that makes any different on what I'm trying to do here (although I'm well aware of the performance problems involved so don't scream at me).

I recognize that it's probably a very odd request. But I'm creating a dynamic query interface that needs this.

Any ideas how to set form properties on a subSubform?

Answer 1

The problem is not that you setting properites of a sub form. The format of:

me.subform1.form.subform2.form.RecordSource = sql

s+Should work just fine.

The problem is you are trying to nest a continues form (or datasheet) in side of a continues for (or a datasheet). This is NOT permitted.

The workaround is to simply place the two sub forms side by side and don’t' nest.

You can still get the second (child child) form to follow by using the link master settings.

In the link child/master settings for child 1, you place:

linkChildFields main_id (whatever is the name of the field in this sub-form that is used to relate back to the parent table) LinkMasterFields [ID]

In the link child/master settings for child 2 form you place

linkChildFields main_id (whatever is the name of the field in this sub-form that is used to relate back to the parent table) LinkMasterFields [child1].[form].[ID] ("masterForm" is the name of the control you used to hold the master form.

So you can model a many to many, but you cannot nest, but as noted the above gives you the same result anyway. So a form will look like this one:

You also likly in the on-current event of the left side (child 1), will want to place a requery of the right side when you navagate - as it often will not update automatic for you.

So:

me.parent.Child2.Requery

Answer 2

Generally the Form property of a subform control gets a "valid reference" only after the subform is shown. Thus, if you want to execute a subSubform.Form access as you do in

Me.subform1.Form.subSubform.Form.RecordSource = ...

you have to make sure that subSubform is visible before the access is executed. The easiest way to do it is to let the subSubform set it's source on his own while it loads (in it's Form_Load sub).

For further details and explanations see my answer in Microsoft Access runtime error 2455 when trying to access grandchildren forms from child form where the problem is the same.