Solved

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

Posted on 2004-04-28
4
401 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 50 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

751 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