By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,482 Members | 3,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,482 IT Pros & Developers. It's quick & easy.

Referencing controls on subform query

P: n/a
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_frmSubCal.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.RowSource = "SELECT ... WHERE (((vCalender.Year)=" & _
"[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

Apr 13 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Apr 13 '06 #2

P: n/a
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!ControlName

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

I also tried Forms("Subform name")!ControlName
and Forms("Subform name").Form!ControlName
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********@hotmail.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


Apr 13 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.