Cath:
Firstly, having 10 separate columns (fields) in the table for the volunteers
is not a good way to go about things. In the jargon its what's known as
'encoding data as column headings'. Data should always be stored as values
at column positions in rows in tables. What you have is a many-to-many
relationship between projects and volunteers in that each volunteer might
attend more than one project, and each project will be attended by more than
one volunteer. A many-to-many relationship is modelled by a third table with
two foreign key columns referencing the primary keys of the other two tables.
So for your purposes you might have the following tables:
Volunteers:
VolunteerID (Primary key)
FirstName
LastName
etc.
Projects:
ProjectID (primary key)
ProjectName
Location
etc.
The primary keys can be autonumber columns. The other columns will each
represent an attribute which is specific to the Volunteers or Projects entity
type. In the jargon these are said to be functionally dependent on the key
of the table, so a particular VolunteerID for instance would always imply the
same FirstName, LastName etc.
The third table to model the relationship between these two might be:
ProjectAttendances:
VolunteerID
ProjectID
AttendanceDate
You'll se that what this table does is resolve the many-to-many relationship
into two one-to-many relationships like this:
Volunteers----<ProjectAttendances>----Projects
The ProjectAttendances table is thus modelling a relationship type, but it
is at the same time modelling an entity type, the attendances by the
volunteers. In the database relational model relationship type is in fact
just a special kind of entity type, so the principle that tables model entity
types and columns model attributes of those entity types still holds true.
If you are recording each volunteer's attendance at a project just once then
the primary key of this table would be a composite one made up of the
VolunteerID and ProjectID columns in combination as these would, as a pair,
always have a unique value in the table. If you are recording multiple
attendances by each volunteer at one project on different dates then the
primary key would be made up of all three columns. In this table VolunteerID
and ProjectID are not autonumbers, however, but straightforward number
columns of long integer data type. The actual numbers are completely
arbitrary and are simply to provide unique identifiers. In fact when
entering or editing data you need never see the numbers or know what they
are, as hopefully will become apparent from what follows.
As regards the form, subform, the main parent form is based ion the
Volunteers table. The subform is based on the ProjectAttendances table and
linked to the parent form by setting the LinkMasterFields and LinkChildFields
properties of the subform control (that's the control in the parent form
which houses the subform) to VolunteerID. With the columns I've suggested
above the subform would best be in continuous form or datasheet view (I
prefer the former as it gives more control over formatting and allows you to
add unbound controls such as command buttons if necessary). The subform
would have just two controls, the first a combo box bound to the ProjectID
column, the second a text box bound to the AttendanceDate column. If there
are other attributes of each volunteer's attendance at a particular project
then you'd have columns for these in the table and controls on the subform
bound to them.
The combo box bound to the ProjectID column would allow you to select a
project by its name, which is done by setting its RowSource property to the
following:
SELECT ProjectID, ProjectName FROM Projects ORDER BY ProjectName;
Other properties of the combo box would be set as follows:
BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm
If you enter the above dimensions for the ColumnWidths property on a system
using Imperial measurements rather than Metric they'll automatically convert
to inches. The second dimension isn't crucial so long as its at least as
wide as the combo box, but the first must be zero to hide the first column
(ProjectID) so you see just the names. The value of the control (and thus
the underlying column to which its bound) will be the hidden ProjectID
however.
Your actual set-up will probably differ from the above, but hopefully you'll
see the principles involved. Remember that if you've used spaces or other
special characters in table or column names you must enclose them in brackets
[like this] when referencing them in VBA code or SQL, e.g. in the RowSource
property above. If in doubt use the brackets.
You'll also need a means to enter rows into the Projects table of course,
which will probably be by means of a separate form. If you wish you can set
things up so that if you type a ProjectName into the combo box on the subform
for a project not yet in the Projects table the projects form will open for
you to enter the project data and then return you to the subform with the new
project in place in the combo box. So as not to confuse things I'll leave
the details of how to do that for the moment, but we can revisit it later if
you wish.
Ken Sheridan
Stafford, England
