Autofill subform based on subform on previous form

[HBTCLaura]

Hi,

I have 3 tables as listed below:

tblClassDate
ClassDateID (PK)
ClassDate

tblStudents
StudentID (PK)
FirstName
Surname

tblAttendance
AttendanceID (PK)
StudentID
ClassDateID
AttendanceAM1
AttendanceAM2
AttendancePM1
AttendancePM2

I have created a form frmClassDate based on tblClassDate with a subform subfrmAttendance in datasheet view to show a list of student names and if they attended each session.

The idea of this is that I can add a new record to frmClassDate for each day and have the list below of students and who has attended. I have gone for this layout as the group changes often so could not be done as a set list.

However, I know it will be annoying to have to add each person every week so I was wondering if there was a way of creating a new record in frmClassDate (which would obvuously bring up a new blank subform) but have the subform subfrmAttendance autofill with the names from the previous record. This way I can remove any who no longer need to attened and add any new attendees without having to add those who are continuing.

I hope this makes sense and I have given all info you need, if not ask away!
I am a novice at VBA but am able to play around with little bits and pieces (I'm assuming it is this sort of coding I'll be needing)

All help welcome, thanks in advance.

Laura

 

[spikepl]

It looks pretty good to me so far. But, there seems to be info missing in the DB -who takes which class, which then should control the recording of attendance. My suggestion:

tblStudentsInClass
StudentInClassID (PK)
ClassID
StudentID
and, fx.
FromDate
ToDate
or a Boolean fieldAttending


Then your tblAttendance would just link to tblStudentsInClass

AttendanceID (PK)
StudentInClassID
AttendanceAM1
AttendanceAM2
AttendancePM1
AttendancePM2

in this way you just need to bring up the attendance lists for the ones that ARE in fact in the class, and yet all the records are still preserved and accessible. And the taking of the class (or not) is set in the tbl StudentsInClass, and appropriate queries present only the attendance records (for viewing or addition) of the students relevant for that class

 

[HBTCLaura]

Thanks for replying!

I have done as you suggested and I think I have relationships right? (see attached screenshot)

I've tried playing around with putting it onto forms and entering data but can't seem to get it to work.

Sorry for being so needy, I like doing these sorts of logical thinking tasks but for some reason my mind just won't let me get a grasp of this one!

 

[spikepl]

The relations look fine.

Forms and subforms could help you out, since they can provide automatic linking between parent tables and child tables.

Some thoughts (untested):

If you wish to record attendance for students attending a given class, you must have a query that selects attandance records for those students that have the given class. So eg, you'd have a main form bound to classes, a subform listing all the studens that attend that class (that would be a subform based on a query constructed by a join between students, classes and studentsinclass.

Then you could hava a second subform, displaying the attendance record of the student selected in the first form. That would be based on a query selecting attencance records of the studentinclass selected n the first subform. The linkaed between these tow can be accomplisshed by putting a textfield on the mainform, and selecting as its control source the field StudentInClassID in the first subform. The second subform would then have this as Link Master Field, and its own StudentInClassID in the LinkChildFields

 

[HBTCLaura]

Thanks for your idea's.

Sorry if I seem a little dim, but, as far as I can see your set up would show the attendance records by person. Is there anyway to show them by Class?

I kind of imagined it to look like the attached, where the subform is filled by a query based on:

[Forms]![frmViewAttendance]![cboClass] =

![tblClass]![ClassName]
and
[Tabel]![tblStudentInClass]![StartDate] <= [Forms]![frmViewAttendance]![txtDate] < [Tabel]![tblStudentInClass]![EndDate]

I've tried this but it doesn't seem to be working...

Thanks for all your help it's greatly appreciated

 

[spikepl]

Your form looks fine

"Not working" contains no information other than that you are unhappy. When asking for assistance, state what you wish, what you have done, and what actually happens with what you have done.

What is

![tblClass]![ClassName] ? You should not have forms named "Table"

To get the display you want, go into the query designer and design the query which is to be the record source for your subform. In the criteria-fields, you can right-click and select Build, thus you can navigate to a control on an open form, the contensof which you wish to use as a criterion. You can get to the query designer by clicking the "..." in the Record Source property of your subform.