Can't reference one subform from another.

[Billy Smith]

I'm having a problem with a small MS Access 2000
database (.mdb).

I have a main form and on it I have two datasheet subforms
I'll call sf1 and sf2. Sf1 and sf2 are both placed on
the main form at the same level as if they are both
children of the main form. I want to use a value from a
record in sf1 to set the RecordSource, i.e. do a query
on sf2. So in the click event proc on sf1 I try to
reference the RecordSource of sf2 but I get nothing
but error messages. For example:

Me.Parent![sf2].RecordSource = strSQL

produces runtime err 2465: "MS Access can't find field sf2"
which is confusing because sf2 is the name of
a subform and not a field.

What is bothering me is that I've searched far and wide
for the rules on how to do this kind of reference and
I can't find them. I've found and tried many other
suggestions, like all of the following:

Parent![sf2].Form!RecordSource
Me.Parent![sf2].Form!RecordSource
Me.Parent![sf2]!RecordSource
Me.Parent![sf2 control name].Form!RecordSource
Parent.Form![sf2]!RecordSource

Can someone tell me where to find the rules for making
this kind of reference to a sibling subform?

Thanks,
Billy

 

[pietlinden]

here's a handy reference
http://www.mvps.org/access/forms/frm0031.htm

 

[Billy Smith]

Yes, I've found that page. It gives some good info but it does not cover my
case: it shows how to do references when one subform is nested within
another. In my case they are both siblings on a main form. I've tried to
use the logic of the methods they show and apply it to my case but
nothing seems to work.

Thanks anyway.

 

[Joan Wild]

I no longer see the original post, but you can apply the logic in that page.

You are on subform1 and want to refer to a control on an adjacent subform2.
Me.Parent!subform2.form!txtSomething

 

[Billy Smith]

As you suggested, I tried these in subform1:

Me.Parent!subform2.form!RecordSource = strSQL
Me.Parent!subform2.form.RecordSource = strSQL

and they both produce the original error message.

To see if subform2 is visible to the main form
I added a command button to the main form and
looked in the click event proc in the debugger.
I found:
1) While in that event proc Forms.Count is 1
2) This line of code produced the same error
message:
Me!subform2.Form.RecordSource = strSQL
3) In the debugger "locals" window I looked
around, including under the Forms section,
and never found the names of the two subforms.

It's beginning to look to me like the subforms
are not visible to the main form. Yet when
I run the main form the subforms appear and
respond to clicks.

Could something be hiding the names of the
subforms from the main form?

 

[Joan Wild]

Is subform2 the name of the control on the main form or is it the name of
the form that the subform control contains? It needs to be the name of the
control.

 

[Billy Smith]

Thanks for your time and patience.

I don't understand what control you're referring to. I'm
trying to set the RecordSource of one of the subforms on
the main form. On my main form I have two subforms
named sf1 and sf2 and no contorols. Each subform
consists of only the text box controls that are part of
the datasheet.

If I'm in VBA code on sf1 trying to set the RecordSource
for the whole sf2, where does a control come in?

--Billy

 

[Billy Smith]

I just found something else that makes me think my problem is
that the main form does not know about the subforms.

The model for the combination of main form and subforms
I'm using is from the Northwind Traders example of the
Customer Orders form. When I look in the debugger locals
window in code on the main form of that example, I clearly
see, under the Me object, the two subforms on the main
form.

When I do that on my main form, there is no reference to
the subforms. Yet the main form must know something
about them because when I run the main form the subforms
also appear and react to user actions.

What's making my subforms partially disappear to the main
form? I created the subforms by first building and testing
them before drag-and-dropping them to the main form. Is
there anything else I have to do to make them visible to
the main form?

--Billy

 

[Joan Wild]

Hi Billy, A subform on a main form is contained in a control. If you open
the Categories Form in Northwind in design view, you'll see it has a subform
in it. The control that contains the subform is named 'Product List'. In
this case that is also the name of the subform, but it needn't be. The
control and the subform in the control can be named differently.

In design view of the main form, click once on the control that contains the
subform - View Properties and look at the title bar of the dialog - that is
the name of the control.

 

[Billy Smith]

I have opened the Northwind Categories form in
design view but I can't find any control containing
the Produce List subform.

I can get the properties box of the subform and the
box is titled "Subform/Subreport: Product List". But
outside the subform all I get is the Detail section
of the main form.

What kind of control is this that contains the subform
and how can I get to it?

--Billy

 

[Billy Smith]

I think I finally got it. It looks like when a subform is placed
onto a form it gets treated as a control. That control, which
is actually the subform, is given a "control name" and the
control name may or may not be the same as the name of the
subform. The control name must be used to reference anything
about the subform.

Thanks to the posters who responded.

--Billy