Before sending your file to me lets step back a bit and examine what's needed
here as I think you'll learn more if you implement it yourself than me doing
it for you.
Firstly the tables we are concerned with here are as follows (your table and
column (field) names may well differ, but hopefully you'll see what's
required).
a. Matches: This has one row per match and its primary key is MatchID.
b. Players: This has one row per player with a primary key PlayerID and
will have columns for the player's name etc.
c. MatchTeams: This has one row for every Match/Player with two foreign
key columns MatchID and PlayerID. In combination the values in each row for
these two columns must be unique, so the two columns together can be the
table's primary key. This table would also have any columns you'd want to
record other attributes of the player's part in this match, e.g. the time he
came on the pitch if a substitute), his position etc.
There will be other tables for Teams, Venues etc of course, but we don't
need to concern ourselves with those for the moment as its how to record the
teams per match which we are looking at.
The main (parent) form will be based on the Matches table and will be where
the data for each match is entered. In fact you'd most probably base it on a
query sorted by MatchDate so the form's records appear in the correct order.
This will be in single form view.
To record the teams for each match you need two subforms in this form. More
accurately you need two subform controls as the underlying form object for
each will be the same, so the first task is to design this form.
The subform will be based on the MatchTeams table and should be in
continuous form view (you can use datasheet view and save yourself some work,
but it looks less professional, so I never do). The form should have
controls bound to all the columns in the table apart from the MatchID and
NUFC columns as the value for these column will be automatically entered by
virtue of the link to the parent form (more of this later). Design the form
so that the column names are in the form header in a single line and all the
controls are in a single line in the detail section. Make the height of the
detail section just big enough to hold the line of controls, i.e. you don't
want any empty space below or above them in the section. To select a player
you'd use a combo box with PlayerID as its ControlSource and a RowSource
which lists the Players. You only want to see there names however, not the
PlayerID value, so the first (bound) column of the list should be hidden.
The combo box wizard can set this up for you, but if you have any problems
post back here. For the moment lets leave this to list all players in the
database; we can com back to limiting the list to only those available for
the current teams/match later.
Having designed this subform, go back to the main matches form and add two
subform controls and size them so they'll each hold the subform you've just
designed and be deep enough to show at least 11 rows in each.
In each of these subform controls select the subform you've just created as
the control's SourceObject property. You'll probably find that Access
automatically inserts MatchID as the LinkChildFields and LinkMasterFields
properties. When you open the form at this stage you'll see all players
you've entered for this match in both subforms, whereas you want to see the
two team lists separately in each. To do this you need to use a bit of
trickery as follows:
The first thing you need to do is add two hidden text boxes to the main
parent form (as they'll be hidden you can put them anywhere on the form).
Set the Name properties to txtTrue and txtFalse, set their ControlSource
properties to =True and =False and set their Visible properties to False (No
in the properties sheet).
The next thing you need to do is amend the LinkChildFiels and
LinkMasterFields properties of the two subform controls. For the Newcastle
subform change them to MatchID;txtTrue for the LinkMasterFields and
MatchID;NUFC for the LinkChildFields. For the opposition's subform change
them to MatchID;txtFalse for the LinkMasterFields and MatchID;NUFC for the
LinkChildFields. I'm assuming that NUFC is a Boolean (Yes/No) column in the
table.
You should now find that the subforms will show the Newcastle and opposition
teams for each match separately in each subform, and when you add a new
player record in the subform the NUFC field will be automatically set to True
or false as appropriate. This is because the two hidden text boxes on the
parent form are acting in the same way as if they were fields in the parent
form's underlying recordset. To use references to controls like this in the
linking properties of a subform you have to eneter their names directly in
the properties sheet, however; you can't do it via the dialogue which open
when you click the build button (the one wuth 3 dots) for the properties.
I think the above should enable you to set up the basic form/subform.
There's other functionality which can be added, but lets not get too far
ahead of ourselves right now (or we'll be flagged off-side!)
I've answered this at some length because it’s an interesting problem and
rather than your sending me your file what I'd like to do, if you are
agreeable, is put together a little demo file which one of my contacts, who
writes for a computer magazine here in the UK, might be interested in putting
in his column (he's not usually averse to getting paid for my work!). I'll
mail you the file directly once I've got it set up – I think I'll base it
Everton, though, not Newcastle United, and maybe put in Liverpool and Man U
to provide some easy opposition<G>.
.