Trying to link a form with a subform

[Mark Gifford]

Hi,

I've inherited an Access database and need to create a form which will
allow me to view and alter the data. I've created a new form and using
a tip I got from this helpful site http://www.jmhare.com/wildhare2.htm
, I've created two combo boxes, with the contents of the second combo
box depending upon the value in the first. That's working fine though
it took a while to get it working properly because I don't think the
database is too correct or efficient (lookups everywhere). I want to
create a subform on that form where one of the fields in the records
is the same value as the second combo box.

Here's the SQL I have so far:

QryCountryCodesByClub
---------------------
SELECT Countries.[CTRY CODE]
FROM Countries INNER JOIN Clubs ON Countries.[CTRY ID] =
Clubs.[CLUBS-COUNTRY_Code]
ORDER BY Countries.[CTRY CODE];

QryCountryCodesByClub (combo box 1)
-----------------------------------
SELECT DISTINCT QryCountryCodesByClub.[CTRY CODE]
FROM QryCountryCodesByClub
ORDER BY QryCountryCodesByClub.[CTRY CODE];

QryClubShortnamesFromCombo1 (combo box 2)
----------------------------------------
SELECT Clubs.CLUBS_Shortname
FROM Countries INNER JOIN Clubs ON Countries.[CTRY ID] =
Clubs.[CLUBS-COUNTRY_Code]
WHERE (((Countries.[CTRY CODE])=[forms]![frmMain]![COMBO1]))
ORDER BY Clubs.CLUBS_Shortname;

The relevant tables are called Personnel, Clubs and Countries. The
Personnel table is basically football players' details, including a
field called PLAYERS-CLUBS_Shortname which is a lookup to the field in
Clubs called CLUBS_Shortname. The Countries table has a three letter
code (CTRY CODE) and the Clubs table has a field (CLUBS-COUNTRY_CODE)
which is a lookup to the CTRY ID identifier in the Countries table. I
want the subform to only show those records where
Personnel.PLAYERS-CLUBS_Shortname = the value in the second combo box
which itself depends upon the value in the first combo box. I hope
there's enough information there. I'd be grateful for any help at all,
been trying to get this to work for a day now.

Cheers

Mark