With the tables as in your reply to Jeff, viz:
Class table
Class ID
Description
Student table
Student ID
Student info
Session table
Session ID
Description
Student Class table
StudentClass ID
Student ID
Class ID
Attendance table
Attendance ID
Session ID
Student ID
Yes/No field
there is no way of relating sessions to classes. The Sessions table needs a
Class ID foreign key column. Is that an accidental omission from the above?
The alternative would be to model a many-to-many relationships between
classes and sessions in the way Steve described. In that model the Session
table would have rows for each 'time-slot' available to all classes, not the
holding of a session for a particular class.
Assuming your model as described in your reply to Jeff and with the Class ID
column in Sessions, rather than Steve's variant, the main parent form would
be based on the Class table, the first subform on the Session table and
linked to the parent form by virtue of the LinkMasterFields and
LinkChildFields properties being set to Class ID
The parent form would contain the hidden text box referencing the Session ID
of the first subform,
The second subform would be based on the Attendance table and its
LinkMasterFields property would reference the hidden text box in the parent
form, its LinkChildFields property would be Session ID.
OK so far?
The drawback of your model is that you need to enter a row in Attendance for
each student enrolled in a class for every session of that class in advance
so that they'll appear in the attendance subform ready for you to check the
check boxes for the students who attend. You can either do this manually,
which would be a PITA, or you can automate it after enrolling a student in a
class, which you'd do in the AfterInsert event procedure of an enrolment form
based on the Student Class table. Before doing that, however you'd need to
have entered rows into the Session table for the class in question, so the
sequence of events from square one would be:
1. Enter a record into the class table for a new class.
2. Enter however many rows are necessary into the session table for the new
class.
3. Enter a record into the Student Class table for a student via an
enrolment form, in whose AfterInsert event procedure you'd run the following
'append' query to insert rows into Attendance:
INSERT INTO [Attendance]([Student ID], [Session ID])
SELECT [Student ID], [Session ID]
FROM [Student Class] INNER JOIN [Session]
ON [Student Class].[Class ID] = [Session].[Class ID]
WHERE [Student Class].[Student ID] = Forms![frmEnrolment]![Student ID]
AND [Session].[Class ID] = Forms![frmEnrolment]![Class ID];
where frmEnrolment is the name of the enrolment form. You'll need to make
sure the table and column names in the query exactly match your actual ones
of course.
Ken Sheridan
Stafford, England
MollyDog8 said:
Hi Ken:
Actually I figured it out (wrote too soon). It works just as you said it
would, which is wonderful.
Sadly, it doesn't do exactly what I hoped for, so I hope you won't mind me
annoying you a little more with this.
I'm afraid I'm really mucking this up trying to describe it, so I here's an
example:
Say I have a class called Math 101.
Mary, John and Sue are registered for Math 101.
Math 101 has 3 sessions: A, B and C.
Mary attended session A, but not B and C. John missed sessions A and C, but
attended session B. Sue attended all three sessions.
If my main form shows Math 101, and the first subform shows the sessions set
up for Math 101 (Session A, Session B and Session C), when I click on Session
A in the subform, I'd like the second subform to show Mary, John and Sue,
giving me the opportunity to click Yes/No for attendance. Thus, when I click
Session B in the first subform, Mary, John and Sue are displayed and I can
indicate in the second subform whether they attended the session.
Does that make any sense?
MD8
Ken Sheridan said:
To correlate two subforms you add a hidden unbound text box control to the
main form which acts as the link between the two. So start by putting a text
box anywhere in the main form, name it txtSessionIDHidden say, and set its
Visible property to False (No).
Set the ControlSource property of the hidden text box so it references the
primary key column of the first (sessions) sub form. To reference a property
of a subform you do so by means of the subform control's Form property.
Subform control here means the control in the main form's Controls collection
which houses the subform, not the underlying form object (though both can
have the same name). So the text box's ControlSource would be something like
this.
=[sfcSessions].Form.[SessionID]
where sfcSessions is the name of the first subform control.
For the second (students enrolled) subform set the subform control's
LinkMasterFields property to the name of the hidden text box:
txtSessionIDHidden
and set its LinkChildFields property to the relevant foreign key column in
the subform's underlying table or query, e.g.
SessionID
As you navigate through the first subform the second should show the
students enrolled for the session currently selected in the first subform.
Ken Sheridan
Stafford, England
MollyDog8 said:
I am building an attendance database for classes. The classes contain
sessions and students may or may not attend all sessions.
I've built a form that contains two subforms. The main form contains only
the class id. The first subform displays the sessions set up for that class
id.
So far, this subform seems to work just fine.
The problem is with the second subform. When a user clicks a session in the
first subform, I want the second subform to display the list of all students
enrolled in the class along with a Yes/No checkbox that can be clicked to
indicate that the student attended the selected session. If the user clicks
another session in the first subform, the list in the second subform again
displays all the students enrolled in the class and enables the user to click
whether the student attended the selected session.
For the life of me I cannot get this thing to work. Any ideas? It is
driving me bananas and I don't think it can be as hard as I'm making it.
Thanks very much!
