Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-25
8
Medium Priority
?
325 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 400 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 1600 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

877 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