Solved

how to pass main form's id to the query?

Posted on 2012-03-25
8
280 Views
Last Modified: 2012-03-26
I have a main form and a suform.
THe subform is from this query which takes a parameter value from the main form's  primary id.

How do I pass that primary id value to the query?
0
Comment
Question by:dkim18
  • 4
  • 3
8 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37764246
Try using the master/child links to create a relationship between main and sub form.

This can be found under the data tab of your subform control as seen in the design view of your main form.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37764261
The above comment assumes you are describing a true subform which is embedded in a main form.  If you are actually dealing with a popup form which is separate from your main form, you can refer to the main form's Id as followa:

Forms!mainformname!YourIdTextboxName
0
 

Author Comment

by:dkim18
ID: 37764271
I am already working with main/subforms but can't figure this out.
The query will be getting a value from the main form (primary key)

The query is like this.
Select AuthorID, Surname from ....Where AuthorID =xxxxx

How should I mark for xxxx? which should come from the main form.
Do I just leave it as where AuthorID = AuthorID.

THis only bring it's own record.
This question is related to my question.

I already linked my main/subform using  master/child link.
It is not bring back all other coauthors.
It brings back itself only.
0
 

Author Comment

by:dkim18
ID: 37764282
I have a main form and a subform.

THis subform is based on this query which will use the main form's primary key.

SELECT Affiliation.AuthorID, Author.Surname, Author.Given_Name
FROM Author INNER JOIN Affiliation ON Author.AuthorID = Affiliation.AuthorID
WHERE (((Affiliation.PublicationID) In (SELECT Affiliation.PublicationID FROM Affiliation WHERE Affiliation.AuthorID=[AuthorID])));
[AuthorID] should be come from the main form.

Not sure how to mark that..
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dkim18
ID: 37764285
THe subform is created from the query which brings back authorid, surname, given name.

THe main form's primary key is linked to this query's authorid.


I want the main form's primary key pass to the query in WHERE statement.
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 100 total points
ID: 37764347
I always recommend to use query builder if you like to get correct syntax.
Create empty query, open it in SQL view, paste:
SELECT Affiliation.PublicationID FROM Affiliation WHERE Affiliation.AuthorID=[AuthorID]
to SQL window, change view to design, select field with criteria: AuthorID, use build. You can select your main form and field. Correct syntax likes to mbizup example:
Forms!mainformname!YourIdTextboxName
0
 
LVL 61

Accepted Solution

by:
mbizup earned 400 total points
ID: 37765056
The problem that you are descibing is to be expected using that particular query with a linked maindorm/subform setup.  The reason is that the Master/Child links restrict the records to only those with the AuthorID shown in the main form(wheras you want it to display records with different authorIDs.

A couple of workarounds - You can try using a popup form instead of a true subform.

Or try this with your existing mainform-subform setup:

1. Remove the master-child links for AuthorID
2. Change your subform query to this:

SELECT Affiliation.AuthorID, Author.Surname, Author.Given_Name
FROM Author INNER JOIN Affiliation ON Author.AuthorID = Affiliation.AuthorID
WHERE Affiliation.PublicationID In (SELECT Affiliation.PublicationID FROM Affiliation WHERE Affiliation.AuthorID = Forms!MainFormName!txtAuthorID);

You will have to supply your own main form name, and "txtAuthorID" should be the name of a textbox on your main form that contais AuthorID.
0
 

Author Closing Comment

by:dkim18
ID: 37765643
Thanks. <br />Removing the link helped.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

919 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

14 Experts available now in Live!

Get 1:1 Help Now