Referring to Controls on a Subform (BUT on SQL)

[sagamw]

I know that a subform on a main form is not a form, but is a control just like a text box etc. and the proper way to refer to it, is

Forms![MyMainForm]![MySubform].Form![SomeID]

To my surprise I can't get this to work, as "criteria" in a SQL query (I have a report taking data from that query).
When I open the report I get a dialog asking for [SomeID]'s value.

I am using a non-english version of Access 2007.

 

[dhookom]

Looks like your syntax should work. Did you use the builder button? Is the name of the control hosting the subform "MySubform"?

Duane
Hook'D on Access
MS Access MVP

 

[sagamw]

Hi Duane. Yes it is. I have already use the same syntax inside my VBA code and it worked fine. I even copy/paste that part of my VBA code into the SQL query.

I am using a non-english version of 2007 (source of many evil things) so I am guessing it has something to do with that.

for example even in the pop-up dialog asking me the value, I am seeing the [forms]!... and the .form parts to change to my native language.

I tried to write it like that, but same result.

I found another post in another forum http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/58831/Referring-to-control-on-subform that uses an "intermediate" text.box for passing the criteria.

It didn't work that either or I haven't done it right.

:-(