Solved

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

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

624 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