Solved

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

Posted on 2004-04-28
4
392 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:robertsfd
  • 2
4 Comments
 
LVL 4

Expert Comment

by:davidW
Comment Utility
robertsfd

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.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 50 total points
Comment Utility
Try:

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

Nic;o)
0
 

Author Comment

by:robertsfd
Comment Utility
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.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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.....

Nic;o)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now