Cascading Form

[Tanya]

Hi
I have a problem with a form and two subforms

Main Form: frmClasses
from table tblClasses and includes [ClassID] and [Class] only

This first subform allows me to see all the students belonging to a particular class. : subfrmClasses
from
SELECT DISTINCTROW [tblStudents&Classes].StudentID, [tblStudents&Classes].ClassID, tblStudents.Code FROM tblStudents INNER JOIN [tblStudents&Classes] ON tblStudents.StudentID=[tblStudents&Classes].StudentID;

Now for the real problem.... I have a table tblBehaviour which needs to be populated each day with information such as attendance and points for good behaviour.
Fields include this sample [there are many more]:
BehaviourID ----------- Autonumber
StudentID ----------- Eg. 12345678 [eight digits]
ClassID ----------- Number
Code ----------- Eg. HPO Abbreviation of Harry Potter
Attendance ----------- selection x,a,e,s etc
BehaviourDate----------- short format
Session ----------- selection 1,2 or 3
Listening ----------- Points scored

the tables are set up as follows:

tblStudents
StudentID --linked to tblBehaviour and Student&Classes 1 to many
LName
FName
Code

tblBehaviour
BehaviourID
StudentID
ClassID
Code
Attendance
BehaviourDate
Session
Listening

tblClasses
ClassID linked to tblStudents&Classes 1 to many
Class


tblStudent&Classes
Student&ClassID
ClassID
StudentID

What I would like to do is select student by 'click' and create a new tblBehaviour record for that day and then be able to populate the rest of the record.

Any thoughts on where I am going wrong?

Appreciate any assistance.

Tanya

 

[MStef]

Hello Tanya Duffy!
Look at "db1Student.mdb" (zip).
I would do it in this way.
Open "frmClasses"
You can see a 3 Navigators, (for 3 tables).
You don't need a tblStudent&Classes.
Look at Tables, Relationships.
I think it is what you need.

 

[Tanya]

Thank you MStef, you have been very helpful. I like the way you approached the problem.

Could you please tell me how to change the orientation of the text in subform2. I have not seen this done before and cannot find the control.

Regards
Tanya

 

[MStef]

In Properties VERTICAL put Yes, in Properties TEXT ALIGN put Center.

 

[Tanya]

Thanks I couldn't find that at first.

Your solution is very good and I will use it but I am sure I have seen databases where a main form has two subforms related if you could enlighten me on this process I would appreciate it.

cheers
Tanya

 

[Tanya]

MStef
I just found a problem with your solution. Each student can only be in one class. I need each student to be in many classes and each class have many students. This was why I had a student&Classes table.
This places me back at square one.
Any suggestions?

Tanya