Auto populate sub records

[dickohead]

Hi guys,

I have a database that stores Exams against a Student and each Exam has Areas:

Student
--> Exams
--> ExamAreas
--> Areas

I have created a form for Student, which has a subform for Exams, and the Exams subform has a subform for ExamAreas.

I can add data to all fields no problem.

What I would like to do now is auto-populate records for my Areas records.

i.e:
If the class is Physics, the areas will be: area1,area7,area9.
If the class is Chemistry, the areas will be: area7, area2, area1

Make sense?

So when I select Physics in my Student subform, the Areas records for the ExamAreas table should auto populate with the areas specified.

Where do I place the logic that says:

If exam.class = "physics" Then
add all [B]Area[/B] records where the specified class is "physics" to the ExamAreas table.
End If

and what might the code look like, i.e. how do I say:

When I choose physics from this list, I want to update this table, with these records.

Sorry if this doesn't read well...

 

[stopher]

First you need to create a table that stores these rules

Subject____Area
Physics____area1
Physics____area7
Physics____area9
Chemistry__area7
Chemistry__area2
Chemistry__area1

Then you use an append query to add the required records to your ExamAreas table. Something like:

INSERT INTO ExamAreas ( subject, area )
SELECT Subject, Area
FROM myRulesTable
WHERE Subject = "Physics"

You can make this query dynamic by referencing a field on a form. You could also incorporate it into VBA and write it "on-the-fly".

When you have appended your records you will probably have to requery the subform to see the records you have added.

You'll also need to consider what should trigger the append. For instance you could just have a button "click here to add areas".

hth
Chris