469,951 Members | 2,352 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

Trying to link a form with a subform

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
Nov 12 '05 #1
0 1314

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Deborah V. Gardner | last post: by
12 posts views Thread by turtle | last post: by
reply views Thread by Mark Gifford | last post: by
reply views Thread by Ace Calhoon | last post: by
6 posts views Thread by Gary T. | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.