Linking a subform to a mainform 1

[khch]

I have a mainform and subform. The mainform has only one field which is
"Date". The subform is in tabular view and contains a list of students
names. What I want to do is taking attendance for the students for every
different date. I need to move from record (Date) to another and add new
records (Dates) in the mainform without the content of the subform changes.
The tables I have are
tblDates: DateId (PK), Day
tblStudents: StudentId (PK), FirstName, LateName
tblStatus: StatusId (PK), Status.

What I have done before is adding DateId as a foreign key in tblStudents and
make a one to many relationship between tblDates and tblStudents. I linked
tblStudents and tblStatus as one to one relationship. Then I made tblDates as
the RecordSource for the mainform and qryStudentsStatus as the RecordSource
for the subform. The subform should contain FirstName, LastName and Status.
Doing so doesn't work as desired. The structure should be like is:
Mainform
Date _________

Subform
Studentname Status*
Name1
Name2
Name3
etc...

* Status is a combobox where the user can choice a value from it.
I'm really get tired of trying to solve the problem...Please help finding a way to do that!
Thanks

 

[PHV]

In fact I guess you need another tablewith at least the following fields:
tblAttendance: DateID (FK), StudentID (FK), StatusID (FK, null allowed)
and an unique index on (DateID, StudentID)

To initially populating this table you may simply consider a cartesian product:
CurrentDB.Execute "INSERT INTO tblAttendance (DateID, StudentID) SELECT DateID, StudentID FROM tblDates, tblStudents"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[khch]

Can you tell me which tables that I need and how to relate them to each other.
Where to put the vba code you suggest:
"CurrentDB.Execute "INSERT INTO tblAttendance (DateID, StudentID) SELECT DateID, StudentID FROM tblDates, tblStudents".

Thanks