A form to easily check off people who attended a course

[Guest]

Hi,

I am designing a database for a client and just wondered if you have
designed a database form like the one I am about to describe and if so, could
I take a look at it or what do I need to do to build the form or perhaps some
site I can go to to see how its done?

The part of the database I am working on now concerns 3 tables: a list of
courses, a list of persons, and a list of persons and what course they
attended.

The user has a paper copy of a course and a list of persons signatures who
completed that course on a certain date. Rather than having the user go into
the list of persons who completed a course and have to, one at a time for
each student who attended the course, pick the person, pick the same course,
pick the date attended....I would like a form whereby the user easily picks
the course once, picks the date attended once, then easily marks a check box
next to each person who attended the course, then clicks a button and as a
result, all the records of people who took that course on a certain date are
filled in the table that contains the list of person and what couse they
completed.

Thank you very much

Craig

 

[Guest]

I think you need to make some modifications to your database design that will
help the process. First, courses are not usually offered only one time.
They can occur at different dates, times, and locations. I would create a
new table of Sessions.
tblSession
SessionID - Autonumber, PK
CourseID - Long FK to course table
SessionDate - Date/Time of Sesion
SessioniLocation

Then rather than a list of people who completed the course, make it a list
of people who registered for the course.

tblRegistrations
RegID - Autonumber, PK
SessionID - FK to session table
StudentID - FK to student table
Completed - Yes/No

Of course you will want a student table to capture the student demographics.

Now, to your specif question, create a form based on a query that joins the
Course and Session tables. I would use combo boxes to look up the course and
the session.

Then create a subform based on the registrations table. Use the subform
control's Link Master, Child fields to relate the registrations for the
current session.
Now all that needs to be done is for the user to look up the student's ID or
name and click the Completed check box control you have put in the subform.

 

[Guest]

Craig,

I'm assuming the date of attendance is in the junction table. I would
approach this by adding a boolean Attended field to the junction table, and
creating a continuous form that includes the attendee's name and the Attended
field. Embed the continuous form as a subform on an unbounded main form.

On the main form, include two unbounded combo boxes, one which selects all
the courses from the junction table, and one that selects all the dates from
the junction table.

Use the AfterUpdate event of each to both filter the other, and, if both are
not null, to filter the records in the subform for that course on that date.
Then you can click the checkboxes and you're done.

' Row Source for first combo
SELECT AttendeeCourse.CourseName FROM AttendeeCourse ORDER BY
AttendeeCourse.CourseName;

' Row Source for second combo
SELECT AttendeeCourse.AttendeeName FROM AttendeeCourse ORDER BY
AttendeeCourse.AttendeeName;


' AfterUpdate code
' No error-handling included
' Assumes the junction table is called AttendeeCourse

' Filter the second combo box
Me![MyDatesComboBox].RowSource = "SELECT AttendeeCourse.AttendeeName FROM
AttendeeCourse WHERE AttendeeCourse.CourseName = " & Me![FirstCombo] & "
ORDER BY AttendeeCourse.AttendeeName;

' Filter the subform
If (Not IsNull(Me![FirstCombo]) AND Not IsNull(Me![SecondCombo])) Then
Me![MySubform].Form.RecordSource = "SELECT AttendeeCourse.AttendeeName,
AttendeeCourse.Attended FROM AttendeeCourse WHERE AttendeeCourse.AttendeeName
= " & Me![FirstCombo] & " AND AttendeeCourse.CourseName = " &
Me![SecondCombo] & ";"
Me![MySubform].Form.Requery
End If

Hope that helps.
Sprinks