Lets assume you have a Students table with columns, StudentID, FirstName,
LastName etc.; a Classes table with columns ClassID and ClassName; and a
table Attendances with columns StudentID , ClassID, AttendanceDate etc
modelling the many-to-many relationship between Students and Classes.
Base the main form on the following query:
SELECT StudentID, (FirstName + " ") & LastName AS FullName
FROM Students
ORDER BY LastName. FirstName;
Set up the main form in Single Form view and include a text box bound to the
FullName column.
Base the subform on the following query:
SELECT Attendances.StudentID, ClassName, AttendanceDate
FROM Attendances INNER JOIN Classes
ON Attendances.ClassID = Classes.ClassID
ORDER BY AttendanceDate DESC;
Include text boxes bound to the ClassName and AttendanceDate columns in the
subform and set it up in Continuous Form view. Link the main and subforms on
the StudentID columns by making theses the LinkMasterFields and
LinkChildFields properties of the subform control in the main form (that's
the control which contains the subform).
You should now see all classes for which the student shown in the main form
is enrolled listed in the subform in descending Attendance date order; you
can change the query's ORDER BY clause to a different order, e.g. ClassName,
if you prefer.
This subform will not allow you to enter data for student Attendances at
classes. For that you'd need to base the form on the Attendances table and
use a combo box bound to the ClassID column rather than having a ClassName
text box, setting up the combo box so it lists the class names. For this the
combo box's properties would be:
ControlSource: ClassID
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm (or rough equivalent in inches, but the first must be
zero)
RowSource: SELECT ClassID, ClassName FROM Classes ORDER BY ClassName;
A text box bound to the AttendanceDate column would be on the subform again
as before.
Remember that if you use spaces or other special characters in object name s
you must enclose the name in brackets, e.g. [Class Name].
Ken Sheridan
Stafford, England
