• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

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

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
dkim18
Asked:
dkim18
  • 4
  • 3
2 Solutions
 
mbizupCommented:
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
 
mbizupCommented:
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
 
dkim18Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dkim18Author Commented:
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
 
dkim18Author Commented:
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
 
als315Commented:
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
 
mbizupCommented:
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
 
dkim18Author Commented:
Thanks. <br />Removing the link helped.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now