Referencing controls on subform query

[Axel]

Hi,

a question about something that seems very simple at first glance:

is it possible to reference other controls of a subform in a query
window without referencing through the parent form? I want to do this
as I want to use same subform on two different parent forms. Main
problem is that the subform is not part of the forms collection.

The only workaround I found was setting the query in code in the
subform_Load event.

example

subform frmSubCal has 2 comboboxes, one for year and one for week
Rowsource of cboYear is based on Calendar.Year
Rowsource of cboWeek is based on Calendar.Week , where Year=cboYear.

when I try to create this condition in query window I have to reference
through the parent form:

.... WHERE Year=[Forms]![frmParent]![frmSubCal]![cboYear]

this will, of course, fail when I drop another instance of the subform
on another parent frmParent2.

is there a way to leave out explicit name *frmParent* or have something
like Me.Parent in there? I also tried creating a public function
getYear on the sub form, referencing like this:
Where YEAR=Form_frmSu bCal.getYear()
but the subform is simply not member of the forms collection.

the only workaround I found was this in the subforms Form_Load event:

Me.cboWeek.RowS ource = "SELECT ... WHERE (((vCalender.Ye ar)=" & _
"[Forms]![" & Me.Parent.Name & "]![frmSubCal].[Form]![cboYear]))"

which appears a bit kludgy; seems to work with multiple instances of
the subform though.

In any case I want to avoid going through global variables or external
routines, the code on the subform should be self contained so that i
could drop the subform onto other forms without additional coding.

tia,
Axel

[pietlinden]

Axel,
FWIW, Keri Hardwick did a really nice summary of referring to various
properties etc of forms and controls with examples, which is here on
AccessWeb. (good place to bookmark... it's the FIRST place I look when
I have a question.)

http://www.mvps.org/access/forms/frm0031.htm

[Axel]

Thanks Piet

for the interesting link. I had a look at this lots of options there
the one I tried

Me!Controlname

works in code alright but *not* in the query designer.

there I have to specify

Forms!Mainform! Subform!Control Name

which kind of defeats the purpose, as the subform could also be on
Mainform2, Mainform3 etc.

I also tried Forms("Subform name")!ControlN ame
and Forms("Subform name").Form!Con trolName
but they all give errors. I think if there was a way to get at the "Me"
keyword in query designer it would be very useful (as the query would
refer to the instance directly, not through the (unknown) parent form)

But my workaround (see original post) works, so not to worry.

regards,
Axel
pi********@hotm ail.com wrote:

Axel,
FWIW, Keri Hardwick did a really nice summary of referring to various
properties etc of forms and controls with examples, which is here on
AccessWeb. (good place to bookmark... it's the FIRST place I look when
I have a question.)

http://www.mvps.org/access/forms/frm0031.htm