Update Subform Query

[Tanya]

Hi
I am trying to create a db for teachers to record grades of students based on numerous assignments and found a sample db for class records on Microsoft site which has been a great help.

My database named Grades.mdb works well up to a point, my stumbling point is with frmAssignments and subfrmAssignmentsResults. The problem is I cannot view the students within the class. The sample database appears to get around this by using a text labeled AssignmentLinkID which I have tried to replicate unsuccessfully. I must be missing something.

I would appreciate if someone could help me with this. Once I have this problem solved I intend to add more fields, allowing more variations to % of grades over different period. First though I need to get over this hump.

Thanks in advance.
Tanya

 

[stopher]

Hi Tanya

First of all your relationship diagram seems a little over cooked:

You have tblResults related to tblStudents and tblAssignments which is fine. But why is it related to tblStudents&Classes?

Why is tblAssignments related to tblStudents&Classes?

You should expect to see the one-to-many symbols on all relationships. This is not the case for the relationship between tblTeachers and tblClasses. Ask yourself why this is.

Anyway, your problem... The reason nothing is displaying in the second subform is because no results have been entered yet (check the results table!). Put some results in and all will be fine.

Tip: I would strongly avoid using non-alphanumeric characters (except underscore) in table or field names e.g. tblStudents&Classes. It could cause you problems later.

Big Tip: If this is a school project you are submitting then I suspect your tutors will easily be able to tell that this is not your own work. Edit: I'm guessing this is for your own use given the time you have spent on the subject (looking back at your posts).

 

[Tanya]

This project is for my own use and what you have seen is only a small portion of a much larger project which is my own work. This project is also my way of learning as I come across new challenges and observe how others have dealt with problems. I have attended two advanced classes on Access Dbs but found them lacking.

When you say, enter some results and all will be fine at first I couln't see how to do this. I then noticed that if I clicked in the subfrmAssignmentsREsults it updated. Another case of learning through exploration and you may have noticed through many of my posts I always give feedback on where I am at and how I followed advise of others, thereby leaving a trail that others may benefit from.

Thank you for your response to my delemma.

Tanya

 

[stopher]

Hi Tanya
Yes sorry, this type of model is a classic school project so alarm bells ring. But from your contribution here it's pretty obvious thats not the case for you.

Ok, here's what's happening...

Initially there are no Results. However, the creator has decided that at some point when you go to review the results, they first need to be populated. By doing so, this gives the user a full results list which they should complete.

It works like this. When you open frmAssignments, subfrmAssignmentResults is empty because at that point there really are no records. But when you click on the subfrmAssignmentResults, there is an On Enter event that fires. This runs the query qryAppendStudentsToResults which adds default results to tblResults and then they are visible in the subform. Take a look at the event and the code behind it.

Another point. As you've discovered, subfrmAssignmentResults does not update like a normal subform would using the master/child link. Instead it is a standalone subform with its source query using AssignmentLinkID as its criteria. It does have some entries for the master/child links but these are redundant.

hope that helps.

Chris

 

[Tanya]

Hi Chris
Thank you for this detail explanation. It is appreciated.
Regards
Tanya

 

[Tanya]

Hi Chris
I am still having problems with this db.

1. It appears I cannot add any more results i.e. only the first student will accept marks.
2. frmstudents, there is a problem with classes, the class appears twice?

Any suggestions on where I am going wrong?

Appreciate any feedback.

kind regards
Tanya

 

[Tanya]

Found the problem relating to subfrmAssignmentsResults. Because I had made some changes to the subform, I needed to delete it from the main form and re-insert it. Now it works well, as expected.

However, I still haven't worked out why I am getting a duplication with frmstudents

 

[stopher]

Sorry Tanya, been a little busy. The reason the subform would not let you enter more than the first line of data was because the master/child properties for the subform were filled in (with some erroneous entries). Once removed it worked ok. Obvious you've sorted it by re-inserting the subform with the same effect. You're just too quick for me

I'll see if I can take a look at your other problem.
Chris

 

[stopher]

Tanya

The problem with multiple classes stems from qryCumulativeClass%. Currently this query is designed to return a line for each DueDate because DueDate is one of the group options. Is this what you want. If you just want a total regardless of DueDate then just remove DueDate from this query.

Do sense check what I have said here because it's a quick answer and I'm not 100% sure.

Chris

 

[Tanya]

Hi Chris
You are wonderful. Thank you so much. Especially how you have explained all these workings to me.
I hope you have a wonderful Christmas.
Kind regards
Tanya