Solved

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

Posted on 2012-03-25
8
316 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 40

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

728 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