Solved

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

Posted on 2012-03-25
8
270 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now