472,141 Members | 1,332 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,141 software developers and data experts.

Trying to link a form with a subform


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:

FROM Countries INNER JOIN Clubs ON Countries.[CTRY ID] =

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

QryClubShortnamesFromCombo1 (combo box 2)
SELECT Clubs.CLUBS_Shortname
FROM Countries INNER JOIN Clubs ON Countries.[CTRY ID] =
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.


Nov 12 '05 #1
0 1411

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 Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.