Basing subform headers on contents of a control

[SusanCB]

Hi, all -- not sure if this can be done, but if it can, you're the ones who will know how.

I am creating a course registration database, and I want to use a subform that will let instructors check off the dates a student has attended class.

On my tblTraining table, there are fields called TrainingDate, TrainingDate2, TrainingDate3 and TrainingDate3. I've created a form that lets users enter the training info, with a subform that lets them record attendance.

I'd like to use the dates entered in TrainingDate, etc., as headers for the attendance subform. That is, users can check off the dates with a check box (the values of which will be stored in tblEnrollment, in the fields Att, Att2, Att3 and Att4).

Not sure I'm being clear, but if anyone has ideas (on how to make this work or on a completely different way to handle it), I'm all ears. Thanks very much.

 

[Jeanette Cunningham]

First - the label that goes with the attendance is a control on your main form.
You can change the caption of that label using code.
Me.[NameOfLabel].Caption = Me.[TrainingDateControlName]

Second - your tblEnrollment is breaking the basic rules of Normalization. Read up on normalization before you go any further.

The idea is that you will replace the field Att, Att2, Att3, Att4 with a single field for attendance and next to the attendance field you put a field for the attendance date.

So you end up with something looking like this:
AttendeeID TrainingDate
1 - - - - - - -2/4/2011
3 - - - - - - -2/4/2011
4 - - - - - - - 5/5/2011

The reality of creating a database to manage enrollments and attendance at training courses is more complicated than the above.

It would be useful for you to search for posts about building a training and enrollment database - by 'useful' I mean that it will save you time and frustration further down the track. Getting the table structure correct right at the start will make it quicker and easier for you to build the forms and reports.

 

[Jeanette Cunningham]

I did a quick search and found these suggestions from access mvp Allen Bronwe.

Answer 1.
To get you started, you will need at least these tables:
Trainee: one record for each person
Training: one record for each type of training
Course: one record for each time a training course is offerred.
Enrolment: one record for each person enrolled in a course.
If you want to track who attended each lecture for the course, and whether
they completed the assessments and graduated from the course, you will need more tables.


Answer 2.
There is quite a bit of work in setting up a training database. As a
minimum, you probably need:
- a table of persons (trainers and trainers)
- a table of units (the subjects taught from time to time)
- a table of classes (each time a subject is offered)
- a table of enrollees (who is enrolled in a class)
You probabaly also need to identify peoples roles, and what units are
expected for each role. From that you can deduce what units each person
should have covered. This will let you identify the gaps (what units they
have not covered, or units where their competency has expired.)
There may be other tables to track attendance, assesssment criteria,
evaluation of each person's criteria, and qualifications/competences earned.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

 

[SusanCB]

Thanks, Jeanette, that worked. I have of course done substantial research on building a registration database and my tables match what you recommended.