How to pass a value from a subform to the main form?

I have a form based on two tables.  I'll call them Table1 and Table2.  It is a Form/SubForm form format with Table1 as the main Form and Table2 as the SubForm.  What I want to do is have an unbound control on the main Form that is populated based on a query that "grabs" a value from the SubForm and uses it as a restriction in the query.  Here is the SQL from the Row Source property for the unbound control on the main Form.

SELECT Table1.Attribute1
FROM Table1
WHERE Table1.Attribute2=[Forms]![SubForm].[Control1].[Value];

(where "Control1" is the name of the control on the subform containing the value I want the query to "grab")

Is there a particular syntax used when specifying to get a value off another form other than just naming that other form as I have done above?  Also, does it matter than the relationship is a one-to-many (table1 can have many related records in table2, but not vice versa) - however, when there are many related records in table2, the "Control1" value for every record is identical?

Thanks for any help/direction.
Who is Participating?

SELECT Table1.Attribute1
FROM Table1
WHERE Table1.Attribute2=[Forms]![Mainform].[SubForm].Form.[Control1].[Value];


I am unsure that i understand the problem but here goes.

If you had a combo box or list box  ( placed in the main form ) populated with all single instances of 'Control1' and linked the subform to this value, would this give you the result you require.

The problem i see with your current process is that once you have selected a 'Control1' you wont see any other values.

If i am totally misunderstanding the problem, please ignore.
robertsfdAuthor Commented:
davidW, sorry I wasn't real clear on my description - thanks for trying though.  I should have probably just asked "from a main form, how do you "address" the value of a control in a subform"

nico5038, thanks - works great.

One thing to note, as I guessed, this doesn't work (at least it didn't for me) when it is a one-to-many relationship, i.e., when the subform can have many records related to the single main form entry, even though the value I wanted to get was the same in every record.  No error is reported, but nothing shows up in the control.  However, with a slight change in design (to a one-to-one relationship) it works as intended.  Thanks again.
Normally the returned value will be that of the "Current" record.
Personally I often use a subforms OnCurrent event to fill a field on the parent (main) form like:

parent.txtField = me.field

Thus the mainform always has the current value available when there's a subformrow present.
For empty subforms this wil however fail.....

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.