Changing Subform RecordSource

[WayneRyan]

Help,

I have a main form with 8 subforms.

Unfortunately, I can't statically link all these forms because some of the
subforms are dependant on a "record-type" indicator in another subform.
They might not have any data at all, or they need data specific to the
"record-type" field in another subform.

I'll be OK if I can just get the syntax for Subform1 to set Subform2's
recordsource and requery it.

So far, from Subform1 (sfrmSubform1):

Forms!frmMain!sfrmSubform2.Form.RecordSource = "Select ..."

Error 2455

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

I looked at the nice FAQ that Bob submitted ... no luck.
I remember reading Stewart's sample on this ... no luck.

Any help greatly appreciated,
Wayne

 

[RoyVidar]

It should be "as easy as"

me.parent!<subformcontrolname>.Form.Recordsource = "SQL...

Sometimes, to get the correct reference, I'll go through the expression builder. Doubleclicking through forms, loaded forms, main form, the subform in question, then select a property/control (recordsource)..., which should usually give correct reference.

 

[boblarson]

Wayne:

The problem is that normally your referencing to subforms via the syntax I provided is supposed to be referencing the subform control and not the subform itself. In this case you really do want to reference the subform itself as the subform control doesn't have a recordsource.

I believe (I haven't tested this one) but to set the recordsource try using:

Forms!frmMain.Form.sfrmSubform2.Recordsource = "Select..."


EDIT: I'm probably wrong on that one as Roy's seems like it makes more sense.

 

[WayneRyan]

Roy & Bob,

The following reference works from the main form.

Forms!frmMain!sfrmSubform2.Form.RecordSource = "Select ..."

But from the subform, that reference won't work. I can use the syntax
Forms!frmMain!sfrmSubform2.Form.Name (from subform1) and it resolves it,
I just can't access the RecordSource.

Syntax aside, I'm wondering if it's possible to set a recordsource in this
manner. I can "see it". I can see some properties. I just can't set the
RecordSource.

I'll keep experimenting with it ... thx for the support.

Wayne

 

[RoyVidar]

Not that this is any comfort, but I've just used both "my usual" syntax (me.parent...), and the one going through the forms collection to alter the recordsource of one subform from another subform here.

If I recall correct, some of the issues that can create challenges here, is if there are any processes going on at the same time with the subform (save, record manipulation...), and/or whether it's linked with the main form/other forms. Could it be an idea to try to assign vbnullstring first? Or remove link child/master?

Edit: oh - and try out some of the ordinary corruption fixes, if you haven't already tried (/decompile, SaveAsText/LoadFromText...)

 

[WayneRyan]

R & B,

This works fine from the main form:

Forms!frmMain!sfrmSubform2.Form.RecordSource = "Select ..."
Forms!frmMain!sfrmSubform2.Form.Requery

But, it throws the 2455 error mentioned earlier when issued from a subform.

Additional note: These are NOT nested subforms. One main form, many peer subforms.

Must leave for a few hours, thanks for the help.
Working Saturdays is not fun anymore.

Wayne

p.s.

Roy, just read your post. This is a brand-new DB, just imported the 10
required tables and was just working on the first pair of subforms.

There are no Master-Child links, because any one of the subforms may or
may not have data in other forms (see record-type) description earlier.

I can't believe that I've never tried this before. The Expression Builder
sees the other subform (and .RecordSource), but throws the error at
run-time.

Thanks again ... I'll do some research later.

Wayne

 

[WayneRyan]

Bob & Roy,

Thanks again for the help. I can't even reproduce the problem now.

My initial syntax works fine, but for some unknown reason it was acting
just like I left the .Form out completely.

Then I'd be referring to the subform object and its properties:

BorderStyle, CanGrow, Enabled, Visible, etc.

With the .Form I correctly get:

Control, AllowEdits, Events, Modules, and Recordsource.

Don't know what happened, but the golf (all 19 holes) seemed to cure the
problem.

Thanks again,
Wayne

 

[boblarson]

Don't know what happened, but the golf (all 19 holes) seemed to cure the
problem.

I'll have to remember that fix if I run into that problem. Sounds like a great way to get things to work

 

[WayneRyan]

Bob, (and Roy)

Well at least I know what happened.

I was in an OnCurrent event where the subform object was present, but not
instantiated yet. The .Form property had no context. All I could reference
were the properties of the subform control.

I can't believe that I couldn't realize that earlier, or hadn't run into it before,
but it's just one more thing to file away. Funny how we can lose a lot of
time on a seemingly simple problem.

Thanks again to both of you.

Guess I'll be working Sunday now, too.

Til next time,
Wayne