Setting up a query for a subform

[gmazza via AccessMonster.com]

Hi there,
I have a main form based on Table 1, as well as a combo box based on Table 2.
I have a continuous subform with info from certain fields in Table 1, and
fields from Table 2.
I just want to choose a Trademark from my combo box on my main form, and the
Trademark, as well as its corresponding info from Table 2, to show up on the
subform. Then I can add onto the subform and this info gets saved into Table
1.

Table 1 - Transaction
Fields - Trademark, Country, FilingNo, ClassNo and a bunch more

Table 2 - TMDetails
Fields - Trademark, Country, FilingNo, ClassNo and a bunch more

So I just want the Trademark, Country, FilingNo, ClassNo record from
TMDetails to show up on the subform. Then I can add to it with the rest of
the fields from Transaction and it all gets saved to the Transaction table.

Can anyone help? I just don't know what to put in the subform's recordsource
to make it work.
Thanks!

 

[Arvin Meyer [MVP]]

Your mistake is in having identical fields in both tables. You only need
them in 1 table. There should be a Primary Key in Table1, linked to a
Foreign Key in Table2. For examples, look at the Northwind sample database
and at Crystal's tutorials:

http://www.accessmvp.com/Strive4Peace/Index.htm

 

[gmazza via AccessMonster.com]

I did as you stated, I got rid of the CountryCode, FilingNo, and ClassNo,
from the Transaction table, and linked the Trademark from the TMDetails to
the Transaction table, with the PK being Trademark in TMDetails and the FK in
Transaction.
What do I do for the form though?
My main form has Transaction as the recordsource.
My combo box on my main form select's Trademark from TMDetails.
My recordsource on the subform doesn't work, displays nothing, here is what I
have:

SELECT TMDetail.Trademark, TMDetail.CountryCode, zcCountry.CountryDesc,
TMDetail.FilingNo, TMDetail.Class, TMDetail.ApplicationNo, TMDetail.
RegistrationNo, Transaction.TrademarkDate, Transaction.InvoiceNo, Transaction.
InvoiceDate, Transaction.InvoiceAmount
FROM Transaction INNER JOIN (zcCountry INNER JOIN TMDetail ON zcCountry.
CountryCode = TMDetail.CountryCode) ON Transaction.Trademark = TMDetail.
Trademark
WHERE (((TMDetail.Trademark)= Forms!Transaction!cboTrademark));

Your mistake is in having identical fields in both tables. You only need
them in 1 table. There should be a Primary Key in Table1, linked to a
Foreign Key in Table2. For examples, look at the Northwind sample database
and at Crystal's tutorials:

http://www.accessmvp.com/Strive4Peace/Index.htm

Hi there,
I have a main form based on Table 1, as well as a combo box based on Table

[quoted text clipped - 24 lines]

to make it work.
Thanks!

 

[Arvin Meyer [MVP]]

You don't need all that. Open the Northwind sample database, that if not
installed, is on the Access/Office disk or on the Microsoft website. You
should be able to get to it from the help menu.

Now look at the Orders and OrderDetails connection is relationships, and the
links in the Orders form. That's a model for how you need to make the
connection.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I did as you stated, I got rid of the CountryCode, FilingNo, and ClassNo,
from the Transaction table, and linked the Trademark from the TMDetails to
the Transaction table, with the PK being Trademark in TMDetails and the FK
in
Transaction.
What do I do for the form though?
My main form has Transaction as the recordsource.
My combo box on my main form select's Trademark from TMDetails.
My recordsource on the subform doesn't work, displays nothing, here is
what I
have:

SELECT TMDetail.Trademark, TMDetail.CountryCode, zcCountry.CountryDesc,
TMDetail.FilingNo, TMDetail.Class, TMDetail.ApplicationNo, TMDetail.
RegistrationNo, Transaction.TrademarkDate, Transaction.InvoiceNo,
Transaction.
InvoiceDate, Transaction.InvoiceAmount
FROM Transaction INNER JOIN (zcCountry INNER JOIN TMDetail ON zcCountry.
CountryCode = TMDetail.CountryCode) ON Transaction.Trademark = TMDetail.
Trademark
WHERE (((TMDetail.Trademark)= Forms!Transaction!cboTrademark));

Your mistake is in having identical fields in both tables. You only need
them in 1 table. There should be a Primary Key in Table1, linked to a
Foreign Key in Table2. For examples, look at the Northwind sample database
and at Crystal's tutorials:

http://www.accessmvp.com/Strive4Peace/Index.htm

Hi there,
I have a main form based on Table 1, as well as a combo box based on
Table

[quoted text clipped - 24 lines]

to make it work.
Thanks!

 

[gmazza via AccessMonster.com]

Sounds good, thanks Arvin.

You don't need all that. Open the Northwind sample database, that if not
installed, is on the Access/Office disk or on the Microsoft website. You
should be able to get to it from the help menu.

Now look at the Orders and OrderDetails connection is relationships, and the
links in the Orders form. That's a model for how you need to make the
connection.

I did as you stated, I got rid of the CountryCode, FilingNo, and ClassNo,
from the Transaction table, and linked the Trademark from the TMDetails to

[quoted text clipped - 30 lines]