Trying to create a subform that updates a table

[Desmond_Fox]

Hi,

I've spent many days trying to work this out. I don't even know if I can explain what I'm trying to do clearly, so here goes.

Any pointers in the right direction would be wonderful.

Here is the set up

1. I have a primary school database.
   
2. There is a main form that shows the students name and class.
   
3. The main form contains tabs for each subject (maths, english etc.). Each tab has a subform that is supposed to allow me to enter a comment about the student's progress.
   
4. My tables are set up properly (as far as I can tell).
   

The tables (simplified)

tbl_Students (has StudentID, StudentName)

tbl_SubjectList (has SubjectID, SubjectName)

tbl_Comments (has CommentID, SubjectID (foreign key), StudentID (foreign key), Comment)

The forms

The Master and Child (main form and subform) are linked using StudentID.

I use a query for fields in the Master form. Each subform has its own query that shows results for a particular subject.

The problem

My subform allows me to edit an existing comment fine (if I enter dummy data directly in a table).

BUT, if there isn't an existing comment, and I attempt to enter one, I get an error - "YOU CANNOT CHANGE A RECORD BECAUSE A RELATED RECORD IS REQUIRED IN TABLE tbl_Subjects."

What I have tried

I understand this error means I am violating referential integrity rules. But I can't see why. The tbl_Subjects is populated with 10 subjects and I am only trying to put a comment against an existing SubjectID.

I've checked that my Form Master / Subform Child is ok. I experimented with having StudentID _and_ Subject_ID to link the master and subform to see if that worked. It only made things worse because my subforms wouldn't display correctly.

Anyway, any tips or pointers in the right direction would really help.

Thanks
Des

 

[ted.martin]

An immediate problem might be that the tbl-SubjectList does not have the StudentID field.

 

[Desmond_Fox]

Thanks. Every student does every subject (since its primary school), so I figured the subject table only needed to have a list of subjects.

If the subject table had a studentID, then the table would then be a list of every studentID for every subject. Is that what I am supposed to do here?

 

[Shuttlekilla]

That doesn't sound right either, if everyone takes all the subjects, why even have a subjects table. Just add comments to individual students as need be. For that matter why have a subjects table. Try recreating the whole thing from scratch using a 2 tables.

tbl_Students (has StudentID, StudentName)(maybe more fields for grades in each subject)

and

tbl_Comments (has CommentID, StudentID (foreign key), Comment)

With this way would have to be more descriptive with your comments, but you can do all kinds of magic with the queries. Wildcard individual subjects with the queries, and use a standardized format for your queries.

 

[Desmond_Fox]

Thanks. I see what you mean about not needing the subjects table.

I've removed that now, but still no joy.

The issue that remains is that I still need to identify whether a comment is about their english, maths, reading etc. so that I can display a form that shows the students name and a comment about just one of the subjects (I've simplified here because the form also shows the student's grades for that subject on the same form).

 

[Desmond_Fox]

[SOLVED - I think] Re: Trying to create a subform that updates a table

Hi,

I got it to work and learnt a few things along the way.

How I got it to work

1. Create a query for each subject (qry_Maths, qry_English). The queries filter the tblComments to just the comments for that subject.

This gives me a query I can use to update the comments table. Today I learnt that you can't create a query with multiple tables (with one-many relationships) and use it to update a table via a form!

2. Make a subform for each subject. The subform includes a 'Comment' field and a SubjectID field.

3. Set the subform SubjectID to the correct number by setting its default value.

This allows me to:
(a) edit a comment about a student's performance on a particular subject
(b) create a new comment about a student's performance on a particular subject
(c) produce easily a report that is formatted just the way I need it.