473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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 1530

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Deborah V. Gardner | last post by:
I am using Access 97 and have had a problem joining a main form and a subform. I have a main form and a subform. The data source on the main form is a SELECT statement referring to three tables....
12
by: turtle | last post by:
I am creating a database that tracks the Techinical Skills of our engineers. There are about about 200 techinical skills that they will need to fill out by giving themselves a ranking. Some...
0
by: Mark Gifford | last post by:
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...
3
by: Mark C | last post by:
I have a unbound form with a tab control with four tabs in an Access 97 database. On each tab I have a sub form each form on the sub forms is bound to its own table. Each table has a field that can...
0
by: Ace Calhoon | last post by:
Hello, Using Access 2003 SP-1. I have a form which contains a subform. Each form represents a table, and is linked by two fields. These fields are also related in the underlying tables, with...
6
by: Gary T. | last post by:
I have 3 tables: tbl_customer -CustID -fkeyMarketingID -CompName -CompAddr -ContactName -ContactAddr -Ph
4
by: MLH | last post by:
I have a subform control on a mainform. Each time I do anything experimentally with it - like even change its Width property, A97 takes the liberty of setting values in the link child field and...
0
by: sparks | last post by:
I went into relationships and set the link between the 2 tables and its 1 to many everything ok. I take a subform and add it as a subform onto form1 the wizard asks about link child and master and...
12
by: banderson | last post by:
Hello all, I am hoping that you can help me with a problem that has occurred numerous times on various forms in the two databases that I am working on. When I first add a subform to a form linking...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.