Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-04-28
4
Medium Priority
?
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Expert Comment

by:davidW
ID: 10945984
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 200 total points
ID: 10946801
Try:

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

Nic;o)
0
 

Author Comment

by:robertsfd
ID: 10948916
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
ID: 10949015
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

719 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