Subform Problem - Junction Table

[Melissa]

I have a many-to-many relationship. My table structure is:

tblClubTeam
ClubTeamID (PK)
ClubTeamAge
ClubTeamName


tblEvaluationSites
EvalSiteID (PK)
SiteName
SiteStartDate
SiteEndDate
SiteCity
SiteState

tblClubTeamTournaments
ClubTeamID (composite PK)
EvalSiteID (composite PK)

This last table is a junction table because one team will
play in many tournaments and one tournament will have many
teams.

In my relationships window, I have created one-to-many
relationships between the first two tables and the
junction table, enforcing referential integrity.

My master form (frmClubTeamTournamentSchedule) is built on
a query containing the necessary fields from tblClubTeam.
(I use concatenation a lot; so I build all forms and
reports off queries.)

I want the subform to allow me to enter each team's
tournament schedule on a continous form. The fields I need
on the subform are all from tblEvaluationSites.
Specifically, SiteName, SiteStartDate, SiteCity,
SiteState. Further, I want to be able to select the
SiteName from a lookup/dropdown/combo box.

I have tried everything I know and then some. My questions
basically boil down to:

1. How do I link the master and subform? I know that they
have to link through the PK field ClubTeamID. The master
form contains ClubTeamID; but the subform only contains
fields from tblEvaluationSites. Do I make the source of
the subform a query built on tblEvaluationSites and my
junction table? If so, which PK fields do I include from
which table?

2. How do I make SiteName in the subform a lookup/dropdown
box from which I can select a specific tournament for that
ClubTeam?

Thank you so much for your help!

 

[John Vinson]

1. How do I link the master and subform? I know that they
have to link through the PK field ClubTeamID. The master
form contains ClubTeamID; but the subform only contains
fields from tblEvaluationSites. Do I make the source of
the subform a query built on tblEvaluationSites and my
junction table? If so, which PK fields do I include from
which table?

Base the Subform *on the junction table*. The Subform should not
contain ANY fields from tblEvaluationSites! Use ClubID as the
Master/Child Link Field and bind a Combo Box to SiteID.

2. How do I make SiteName in the subform a lookup/dropdown
box from which I can select a specific tournament for that
ClubTeam?

Create a Combo Box based on tblEvaluationSites; include all the
columns that you want to see in the subform. Make SiteID the bound
column, but set its width to zero in the ColumnWidths property,
leaving SiteName as the first (or only) visible column. You can put
textboxes on the subform with Control Sources such as

=cboSiteID.Column(n)

where (n) is the zero based subscript of the field in the combo's
rowsource. This will echo the address or whatever from the selected
site.

 

[Guest]

John

Thank you so much. It took me a while, but I have it working. I have two other questions now

1. The combo box I created consists of 4 columns (SiteName, SiteLocation, SiteDates, SiteID). SiteDates is concatenated and is = [SiteStartDate] + "-" + [SiteEndDate]. I want the subform ordered by SiteStartDate DESC. But, that is not a column in my combo box. I tried sorting on the concatenated SiteDates, but it doesn't work. Any ideas

2. After I get all of the Club Team schedules entered, I will need a report. The format of the report will be

SiteDate - ascending order based on parameter query of Between [Enter Starting Date] and [Enter Ending Date
Site - alph
ClubTeam - alph
Recruits - alph

My question: Will this be possible? I will basically have a subreport within a subreport within a subreport. Will I have to use the junction table as the source for any of the subreports, i.e., to provide the link between Site and Club Team? Do I do it the same as on the form - meaning that I have no fields from Club Team on that subreport but have to do text boxes bound columns from a cboClubTeam that I create? If that is the case, do I simply include all ClubTeam fields that I will need in the cbo and set their column width to 0"

Thanks again. You have been a lifesaver!

 

[John Vinson]

John:

Thank you so much. It took me a while, but I have it working. I have two other questions now.

1. The combo box I created consists of 4 columns (SiteName, SiteLocation, SiteDates, SiteID). SiteDates is concatenated and is = [SiteStartDate] + "-" + [SiteEndDate]. I want the subform ordered by SiteStartDate DESC. But, that is not a column in my combo box. I tried sorting on the concatenated SiteDates, but it doesn't work. Any ideas?

Add SiteStartDate to the query, sort by it descending, but uncheck the
Show box. It won't be included in the combo but it will sort
correctly.

2. After I get all of the Club Team schedules entered, I will need a report. The format of the report will be:

SiteDate - ascending order based on parameter query of Between [Enter Starting Date] and [Enter Ending Date]
Site - alpha
ClubTeam - alpha
Recruits - alpha

My question: Will this be possible? I will basically have a subreport within a subreport within a subreport. Will I have to use the junction table as the source for any of the subreports, i.e., to provide the link between Site and Club Team? Do I do it the same as on the form - meaning that I have no fields from Club Team on that subreport but have to do text boxes bound columns from a cboClubTeam that I create? If that is the case, do I simply include all ClubTeam fields that I will need in the cbo and set their column width to 0"?

You may not need any subreports at all! Reports have a quite powerful
"Sorting and Grouping" feature. You can group the report by sitedate,
and then by site, and then by clubteam. The Report should be based on
a query joining ALL THREE tables.

 

[Melissa]

John:

Report worked like a charm. However, on the subform...

The cboTournament consisting of SiteName, SiteLocation,
SiteDates, & SiteID needs to sort alpha on SiteName for
lookup and data entry. Once the schedule for that team
has been entered, I need the subform entries to sort DESC
on SiteDates.

On the subform properties sheet, I originally tried
entering cboTournament.Column(2) DESC That didn't work.

I tried adding another column for StartDate as you
suggested - putting its width at 0" so it wouldn't
appear. So I changed the cbo to 5 columns so I could
include StartDate as the 5th column, still binding the
4th column. I changed sort order in the query (DESC on
StartDate) but then the lookup is not alphabetized by
SiteName. Next, I removed the DESC on Start Date and
entered cboTournament.Column(4) DESC in OrderBy on the
subform property sheet. No matter what I have tried on
OrderBy has not worked.

Any more ideas?

Can't thank you enough for your help!

-----Original Message-----

(SiteName, SiteLocation, SiteDates, SiteID). SiteDates is
concatenated and is = [SiteStartDate] + "-" +
[SiteEndDate]. I want the subform ordered by
SiteStartDate DESC. But, that is not a column in my combo
box. I tried sorting on the concatenated SiteDates, but
it doesn't work. Any ideas?

Add SiteStartDate to the query, sort by it descending, but uncheck the
Show box. It won't be included in the combo but it will sort

correctly.

2. After I get all of the Club Team schedules entered, I will need a report. The format of the report will be:

SiteDate - ascending order based on parameter query of Between [Enter Starting Date] and [Enter Ending Date]
Site - alpha
ClubTeam - alpha
Recruits - alpha

My question: Will this be possible? I will basically

have a subreport within a subreport within a subreport.
Will I have to use the junction table as the source for
any of the subreports, i.e., to provide the link between
Site and Club Team? Do I do it the same as on the form -
meaning that I have no fields from Club Team on that
subreport but have to do text boxes bound columns from a
cboClubTeam that I create? If that is the case, do I
simply include all ClubTeam fields that I will need in
the cbo and set their column width to 0"?

 

[John Vinson]

The cboTournament consisting of SiteName, SiteLocation,
SiteDates, & SiteID needs to sort alpha on SiteName for
lookup and data entry. Once the schedule for that team
has been entered, I need the subform entries to sort DESC
on SiteDates.

The Combo and the Subform are two separate objects; they can and
should be based on two separate queries. The Combo's Rowsource query
should be sorted by sitename; the Subform should be based on a query
sorted by SiteDate. It may be necessary to include two tables - the
junction table and the site table - in the Subform's recordsource to
do this; if you do, you can display the site fields directly from the
site table, rather than from the combo - but it's best to make those
controls non-updateable. Set their Enabled and Tab Stop properties to
No and the Locked property to Yes.